why wont this work...

[nerd19]

i have a form that has several options (cmboboxes, text, date and time picker) and depending on what options are select a make a sql string to query a table. my problem is when i add a between dates criteria to the string it breaks.

here is an example of a string that wont work, it states that it is missing the two parameters of the between criteria:
SELECT * FROM tblChanges_local WHERE ((tblChanges_local.numOperation) = 'Reformer') AND ((tblChanges_local.txtType) = 'Reformer Cartridge') AND ((tblChanges_local.dateInstall) Between [Forms]![NeckerTools].[startDate] And [Forms]![NeckerTools].[endDate]) ORDER BY tblChanges_local.numNecker, tblChanges_local.numOperation, tblChanges_local.numPocket, tblChanges_local.txtType, tblChanges_local.dateInstall DESC;

to me i see nothing wrong, but i have been trying to get this one last thing to work for the last day or so.


the following is my code to create the string. the boolean variables are set so that if the corresponding data field on the form is blank that particular criteria will be left out of the search.

sqlNeck = "SELECT * FROM tblChanges_local"

If noVars = False Then
sqlNeck = sqlNeck + " WHERE "
End If
If noNeck = False Then
sqlNeck = sqlNeck + " ((tblChanges_local.numNecker) = '" & numNeck & "')"
End If

If noOp = False Then
If noNeck = False Then
sqlNeck = sqlNeck + " AND "
End If
sqlNeck = sqlNeck + " ((tblChanges_local.numOperation) = '" & numOperation & "')"
End If

If noPock = False Then
If (noNeck = False) Or (noOp = False) Then
sqlNeck = sqlNeck + " AND "
End If
sqlNeck = sqlNeck + " ((tblChanges_local.numPocket) = '" & numPock & "')"
End If

If noType = False Then
If (noNeck = False) Or (noOp = False) Or (noPock = False) Then
sqlNeck = sqlNeck + " AND "
End If
sqlNeck = sqlNeck + " ((tblChanges_local.txtType) = '" & txtType & "')"
End If

If noSerial = False Then
If (noNeck = False) Or (noOp = False) Or (noPock = False) Or (noType = False) Then
sqlNeck = sqlNeck + " AND "
End If
sqlNeck = sqlNeck + " ((tblChanges_local.txtSerial) = '" & txtSerial & "')"
End If

If Me.chkEnable.Value = -1 Then
If (noNeck = False) Or (noOp = False) Or (noPock = False) Or (noType = False) Or (noSerial = False) Then
sqlNeck = sqlNeck + " AND "
End If
sqlNeck = sqlNeck + "((tblChanges_local.dateInstall) Between [Forms]![NeckerTools].[startDate] And [Forms]![NeckerTools].[endDate])"
End If

If noVars = False Then
sqlNeck = sqlNeck + " ORDER BY tblChanges_local.numNecker, " & _
"tblChanges_local.numOperation, tblChanges_local.numPocket, " & _
"tblChanges_local.txtType, tblChanges_local.dateInstall DESC;"
Else
sqlNeck = sqlNeck + " ORDER BY tblChanges_local.dateInstall DESC;"
End If

 

[lameid]

While + is an acceptable concantenation operator, I prefer &. It does not really matter but you are mixing them which can be confusing.

You seem to have something equivalent too...

sqlNeck = sqlNeck + "((tblChanges_local.dateInstall) Between [Forms]![NeckerTools].[startDate] " & _ 
      "And [Forms]![NeckerTools].[endDate])"

It appears it should work but my guess it is having trouble with the dates. Try the following...

sqlNeck = sqlNeck + "((tblChanges_local.dateInstall) Between #" & [Forms]![NeckerTools].[startDate] & "#" & _ 
      "And #" & [Forms]![NeckerTools].[endDate] & "#)"

You will end up with SQL with your date literals in it, much like you are doing for your other criteria.

 

[nerd19]

cool, that works. Thanks a ton. Leaves me with anoth question, whats the difference with the two as a concatenation operator?

 

[lameid]

OOPs I did it too as I didn't change the + when I modified your code...

The & is definiatively a concantenation operator where as + is also arithmetic. If you wanted to concatenate two numbers and used +, you might add them. Where as with & it would force the implicit conversion to a string (text) first. The only way to 'add' strings together is to concatenate them. Some other programming languages use only + to concatenate (they also do not do implicit conversions, instead you have to explicitly use a function).

 

[JoeAtWork]

You will also want to make sure your dates are formatted as mm/dd/yyyy.

"The sum of " & 1 & 1

Has the result: "The sum of 11"

"The sum of " + 1 + 1

Will give you an error