Comma-delimited list - Query

[vbJupiter]

I have a public function in a standard module that adds records to a table. The function returns a comma-delimited list of the batch numbers that it added to the table including leading and trailing commas.

I am trying to run a query using the list to select just those records that are in the list.

SELECT * FROM AssignedBatches
WHERE GetBatchNumbers() Like '*,' & BatchNum & ',*';

GetBatchNumbers is the function that returns the comma-delimited list. And BatchNum is simply a field in the AssignedBatches table.

I am not getting any errors when I run the query. It simply opens an empty recordset.

This is my first time using a standard module, so I don't know if I'm missing something module-related or not. I've tested the function and it is indeed returning a list as it should, i.e: ,828,829,830, etc...

Thanks for your help!
AA

 

[KeithG]

Can you post the code behind your function. It seems you are not looking for an exact match, is this correct?

 

[vbJupiter]

Yes I am actually looking for an exact match...


Public Function GetBatchNumbers() As String

Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strBatchNumbers() As String
Dim strSQL As String
Dim strCriteria As String
Dim strBatchNoList As String
Dim intCount As Integer
Dim intCounter As Integer

intCount = Forms![Assign Batch]!txtQuantity

strSQL = "SELECT TOP " & intCount & _
" MasterBatch.BatchNumber, MasterBatch.BatchNum " & _
"FROM MasterBatch LEFT JOIN AssignedBatches " & _
"ON MasterBatch.BatchNum = AssignedBatches.BatchNum " & _
"WHERE (AssignedBatches.BatchNum is Null) AND "

Select Case Forms![Assign Batch]!optTranType
'This is part of the criteria for what batch numbers get selected. More pertinent to the AssignBatch procedure below...

Case 1:
'Override Batch Type has been selected
strCriteria = "BatchNumber Like '4*' Or BatchNumber Like '5*'"

Case 2:
'Override Payoff has been selected
strCriteria = "BatchNumber Like '8*'"

Case 3:
Select Case [Forms]![Assign Batch]![cboNonOverride]
Case "Analysis"
strCriteria = "BatchNumber Like 'A*'"

'other cases here
End Select
End Select

ReDim strBatchNumbers(1 To intCount)

Set rs = CurrentDb.OpenRecordset(strSQL & strCriteria, dbOpenSnapshot)

If rs.EOF Then
MsgBox "There are no available batch numbers for this type", vbCritical
'error handling here
Else
For intCounter = 1 To intCount

If rs.EOF Then
MsgBox "There was an error processing the request. There are not enough batch numbers to assign", vbCritical
'error handling here
Else
strBatchNumbers(intCounter) = rs!BatchNum
'strBatchNoList keeps track of the batch numbers being added, starting the comma-delimited list
strBatchNoList = strBatchNoList & "," & rs!BatchNum

rs.MoveNext
End If

Next intCounter
End If

rs.close

AssignBatch strBatchNumbers

'add the trailing comma to the comma-delimited list, and pass value to function
GetBatchNumbers = strBatchNoList & ","


DoCmd.OpenQuery "ViewTest5", acViewNormal, acReadOnly

' I've tried to do run the query as qdf object, and I get the same results...no error message, just an empty recordset

End Function

end code
---------------------------------------

Private Sub AssignBatch(strBatchNumbers() As String)

'this is the procedure that adds adds records to the AssignedBatch table.

end sub

 

[pdx_man]

Wow, I've been looking at your code and it is confusing.

Is the end-goal here to return the BatchNums that have been newly entered into the AssignedBatches table?

It appears you first are getting #n of the batches in the MasterBatch table that are not in the AssignedBatches table. Then, you are building an array of the batch numbers and a CSV of the batch numbers. You then send the array to a Sub where they are added to the AssignedBatches table. You set the function to the CSV list and call a query ViewTest5 which we know nothing about. Is ViewTest5:

SELECT * FROM AssignedBatches
WHERE GetBatchNumbers() Like '*,' & BatchNum & ',*';

??

Which you are calling from within the function?

Just know the GetBatchNumbers() function will be called for each record in the AssignedBatches table, for which all of the batches will be entered into the AssignedBatches table and subsequent calls to the function will yield no results, therefore an empty string to compare against Batchnum.

Maybe I am seeing this wrong, and I don't mean to belittle you, I just need to know how to approach your situation. What is your DB and programming experience? I'm thinking there is a far better solution, but I may be missing the bigger picture.

 

[vbJupiter]

Wow, I've been looking at your code and it is confusing.

My apologies. I should have added more notes.

Is the end-goal here to return the BatchNums that have been newly entered into the AssignedBatches table?

Yes, my end goal is to retrieve the records newly entered to the AssignedBatches table.

It appears you first are getting #n of the batches in the MasterBatch table that are not in the AssignedBatches table. Then, you are building an array of the batch numbers and a CSV of the batch numbers. You then send the array to a Sub where they are added to the AssignedBatches table. You set the function to the CSV list and call a query ViewTest5 which we know nothing about. Is ViewTest5:

Yes, this is correct.

SELECT * FROM AssignedBatches
WHERE GetBatchNumbers() Like '*,' & BatchNum & ',*';

Yes, this is correct.

Which you are calling from within the function?

bad idea i'm assuming. i originally had it in the assignbatch subroutine, but moved it to the function when i was troubleshooting.

Just know the GetBatchNumbers() function will be called for each record in the AssignedBatches table, for which all of the batches will be entered into the AssignedBatches table and subsequent calls to the function will yield no results, therefore an empty string to compare against Batchnum.

I'm a little confused by what you are saying here. I define the basic sql statement which is basically asking for all the available batch numbers; however, I don't open this recordset. I add more criteria first as defined by Case Statements before opening the recordset. In terms of subsequent calls to the function not returning any results, that's what I want. This function is designed for the main Assign Batch form, so the user just needs to see the records that they just entered into the form. Therefore, I don't want the function to store the records.

Maybe I am seeing this wrong, and I don't mean to belittle you, I just need to know how to approach your situation. What is your DB and programming experience? I'm thinking there is a far better solution, but I may be missing the bigger picture.

I haven't taken it as such. If you have can think of a better way, I'm all ears.

 

[pdx_man]

Well, here are my thoughts.
Change this function to a SUB and call it from your form.

Don't add the records in another routine, do it in your SQL Statement and add a BatchAddNum field to your AssignedBatches table:

Dim LastAdd Long

LastAdd = DMAX("BatchAddNum", "AssignedBatches")

strSQL = "INSERT INTO AssignedBatches (BatchNumber, BatchNum, BatchAddNum " & _
"SELECT TOP " & intCount & _
" MasterBatch.BatchNumber, MasterBatch.BatchNum " & , LastAdd + 1 & _
"FROM MasterBatch LEFT JOIN AssignedBatches " & _
"ON MasterBatch.BatchNum = AssignedBatches.BatchNum " & _
"WHERE (AssignedBatches.BatchNum is Null) AND "

Select Case Forms![Assign Batch]!optTranType
'This is part of the criteria for what batch numbers get selected. More pertinent to the AssignBatch procedure below...

Case 1:
'Override Batch Type has been selected
strCriteria = "BatchNumber Like '4*' Or BatchNumber Like '5*'"

Case 2:
'Override Payoff has been selected
strCriteria = "BatchNumber Like '8*'"

Case 3:
Select Case [Forms]![Assign Batch]![cboNonOverride]
Case "Analysis"
strCriteria = "BatchNumber Like 'A*'"

'other cases here
End Select
End Select

docmd.RunSQL (strSQL & strCriteria)

END SUB

Then have your query be something like

SELECT * FROM AssignedBatches
WHERE BatchAddNum = (SELECT MAX(BatchAddNum) FROM AssignedBatches)

 

[vbJupiter]

Thanks pdx man. I really appreciate your response. I was able to get the query working to display the recently added records. I think my problem was that I was working too quickly and wasn't being thorough enough. I simply moved the docmd.openquery line to the main form code module and it works.

I will look into your suggestion because it seems like it may be a better solution as selects the records and assigns it all in one shot. I would think that would make it run faster.

I get the overall idea...just need to look into the dmax function as I'm not familiar with it.

Thanks again and I'll keep you posted.

AA

 

[vbJupiter]

SELECT * FROM AssignedBatches
WHERE BatchAddNum = (SELECT MAX(BatchAddNum) FROM AssignedBatches)

I'm editing this post. At first I thought this query wouldn't work for me because the MAX would return just one record. Now that I'm looking at it again, the insert into query would give each batch number the same LastAdd value, so the MAX function should pull up all the records in the batch. The only downside I can see then involved the DMAX function which I've read might cause confusion if there's multiple users adding data to the db at the same time. This is a possible scenario for this particular db. It would be very confusing for the user to see totally unrelated records! I am going to look into though because I am sure there's a workaround.

If I have form field data to add to the assignedbatches table, I would need a second action query to add these records, is that correct?
ie: AssignedBatches.StartDate = Forms!Assign Batch!txtStartDate
(this example uses date/time format, I just have it labeled txtStartDate because it is a textbox)

Thanks!
AA

 

[Bodisathva]

maybe I'm missing something, but why not:

SELECT * FROM AssignedBatches
WHERE BatchNum IN (" & GetBatchNumbers() & ")"

 

[vbJupiter]

maybe I'm missing something, but why not:

SELECT * FROM AssignedBatches
WHERE BatchNum IN (" & GetBatchNumbers() & ")"

Hi Buddha,

I tested this to see if it would work and it just pulls up an empty recordset.

I think that for the In command to work, I would need to have the array include the "" for each batch number in addition to the commas I am already adding.

Let's say the Query is evaluating record batchnum 525 of the batchnum field. It would look like this:

WHERE 525 IN (",525,526,527");

It can't compare the two in that way. It needs to see it like:

WHERE 525 IN ("525", "526", "527");

so like I said, I would need to add quotes in addition to commas.

 

[Bodisathva]

Hi Buddha,

um...Bodi will do...wrong doctrine

It can't compare the two in that way. It needs to see it like:

WHERE 525 IN ("525", "526", "527");

so like I said, I would need to add quotes in addition to commas.

This would only be the case if your batch numbers were strings, not numbers, in which case, your SQL statement is looking for a number in a list of strings...you can't do that. Either set the value searching for to a string, or remove the quotes surrounding the IN clause.

another point, you only need to place single quotes around the individual strings within the IN clause

 

[vbJupiter]

um...Bodi will do...wrong doctrine
This would only be the case if your batch numbers were strings, not numbers, in which case, your SQL statement is looking for a number in a list of strings...you can't do that. Either set the value searching for to a string, or remove the quotes surrounding the IN clause.

another point, you only need to place single quotes around the individual strings within the IN clause

My apologies, Bodi. I am in such a hurry today, I'm embarrassed I didn't catch that...

Would you mind showing me the sql syntax using the In command when comparing to a string? You said to remove the quotes surrounding the In clause, but I can't seem to get it to work.

 

[Bodisathva]

when checking for a string:

"SELECT * FROM thisTable WHERE fieldKey IN ('String1', 'String2', 'String3')"

when checking for a number:

"SELECT * FROM thisTable WHERE fieldKey IN (1,2,3,4,5)"

note that the entire SQL statement is enclosed within the double quotes (since technically it is a string) and any strings within the SQL statement are enclosed in single quotes.