Multiple Criteria Form error 1

[KellyNewUser]

Hi, I am trying to create a form with multiple criteria. I am adapting the code below from a tutorial. I am getting an error for "Syntax error in From Clause". Can anyone see a reason for this.
Kelly



Private Sub Command19_Click()

Dim sSQL As String, sLen As Integer
sSQL = "SELECT products.* FROM Products"
If Not IsNull(Me.NDC_Number) Then
sSQL = sSQL & "(((Products.NDC_Number)=[Forms]![QBF_Form]![NDC_Number])) AND "
End If
If Not IsNull(Me.ProductName) Then
sSQL = sSQL & " (((Products.ProductName)=[Forms]![QBF_Form]![ProductName])) AND "
DoCmd.OpenForm "browse_products"
End If
' The following "IF...then" sequence detects for "AND" at the end of the SQL
' statements and removes it if it exists. This prevents a "syntax error."
If Right(sSQL, 3) = "ND " Then
sLen = Len(sSQL)
sSQL = Left(sSQL, sLen - 4)
End If
Forms![browse_products].Form.RecordSource = sSQL
End Sub

 

[glalsop]

You seem to be missing the WHERE clause.

try changing this:
sSQL = "SELECT products.* FROM Products"

to this:
sSQL = "SELECT products.* FROM Products WHERE "


-Gary

 

[KellyNewUser]

Thanks! That got rid of the error. Now I am not getting any results. The "browse_products" form is opening with nothing in it. Any ideas why?

Kelly

 

[glalsop]

Try this:

At the end of the procedure, right before this line

Forms![browse_products].Form.RecordSource = sSQL

put in this line

Debug.Print sSQL

This will print your SQL statement in the immediate window of the code module (To access this go back into the code module and press ctl - G)

Now you can see your SQL statement.

If you can't see the error, then copy the SQL string from the immediate window and paste it back here.

Good luck



-Gary

 

[glalsop]

Oops...

first try changing this:

sSQL = sSQL & "(((Products.NDC_Number)=[Forms]![QBF_Form]![NDC_Number])) AND "

to this:

sSQL = sSQL & "(((Products.NDC_Number)= " & [Forms]![QBF_Form]![NDC_Number] & ")) AND "

This assumes that NDC_Number is a number field.

Also change this:

sSQL = sSQL & " (((Products.ProductName)=[Forms]![QBF_Form]![ProductName])) AND "

to this:

sSQL = sSQL & " (((Products.ProductName)= '" & [Forms]![QBF_Form]![ProductName] & "')) AND "

This assumes that Product name is a text field.

Good luck




-Gary

 

[KellyNewUser]

Okay. This is the code as it stands. It seems to be having a problems with the

Forms![browse_products].Form.RecordSource = sSQL

line of code. Also when did the print sql screen it returned the code

SELECT products.* FROM Products WHERE

let me know what you think.
Kelly

Private Sub Command19_Click()

Dim sSQL As String, sLen As Integer
sSQL = "SELECT products.* FROM Products WHERE "
If Not IsNull(Me.NDC_Number) Then
sSQL = sSQL & "(((Products.NDC_Number)= " & [Forms]![QBF_Form]![NDC_Number] & ")) AND "
End If
If Not IsNull(Me.ProductName) Then
sSQL = sSQL & " (((Products.ProductName)= '" & [Forms]![QBF_Form]![ProductName] & "')) AND "
DoCmd.OpenForm "browse_products"
End If
' The following "IF...then" sequence detects for "AND" at the end of the SQL
' statements and removes it if it exists. This prevents a "syntax error."
If Right(sSQL, 3) = "ND " Then
sLen = Len(sSQL)
sSQL = Left(sSQL, sLen - 4)
End If
Debug.Print sSQL
Forms![browse_products].Form.RecordSource = sSQL
End Sub

 

[glalsop]

Okay, first problem is that you are not appending anything to your SQL string if Me.NDC_Number IS Null. Same with Me.ProductName.

So try changing this:

If Not IsNull(Me.NDC_Number) Then
sSQL = sSQL & "(((Products.NDC_Number)= " & [Forms]![QBF_Form]![NDC_Number] & ")) AND "
End If

To this:

If Not IsNull(Me.NDC_Number) Then
   sSQL = sSQL & "(((Products.NDC_Number)= " & [Forms]![QBF_Form]![NDC_Number] & ")) AND "
Else
   sSQL = sSQL & " (1=1) AND "
End If

And this:

If Not IsNull(Me.ProductName) Then
sSQL = sSQL & " (((Products.ProductName)= '" & [Forms]![QBF_Form]![ProductName] & "')) AND "
DoCmd.OpenForm "browse_products"
End If

To this:

If Not IsNull(Me.ProductName) Then
   sSQL = sSQL & " (((Products.ProductName)= '" & [Forms]![QBF_Form]![ProductName] & "')) AND "
Else
   sSQL = sSQL & " (1=1) AND "
End If

I removed the DoCmd.OpenForm "browse_products"
statement, because I'm not sure why it was there.

After those changes, run the procedure again and re-post the new SQL statement. Hopefully you will have a WHERE clause now and we will be on our way.

Good luck




-Gary

 

[KellyNewUser]

I inserted the code you gave me and ran it again. This is what showed up in the Ctrl-G window.

SELECT products.* FROM Products WHERE (1=1) AND (1=1)
SELECT products.* FROM Products WHERE

What are your thoughts?
Kelly

 

[glalsop]

Okay, the top one looks like the one we want to me...

This means that NDC_Number and ProductName were both left blank on the selection form. The 1=1 is just a way to select everything, since a value was not supplied.

So now I think that we can open the browse form and supply sSQL as our record source. So try replacing

Forms![browse_products].Form.RecordSource = sSQL

With:

DoCmd.OpenForm "browse_products", acNormal
Forms![browse_products].Form.RecordSource = sSQL

Does this work?



-Gary

 

[KellyNewUser]

I altered the code like you told me to. Now the browse_products form opens but it is empty. Also if there is anything in the NDC_Number field I get an error that says "You canceled the previous operation". Let me know what you think. I really appreciate all the help you have given me with this problem.

Kelly

 

[glalsop]

Try posting the sSQL strings that get generated in 3 seperate situations:

1) both fields are left blank on QBF_Form
2) you select a value for both fields on QBF_Form
3) you select a value for NDC_Number but leave ProductName blank

-Gary

 

[KellyNewUser]

SELECT products.* FROM Products WHERE (1=1) AND (1=1)
SELECT products.* FROM Products WHERE (((Products.NDC_Number)= 005915430)) AND (1=1)
SELECT products.* FROM Products WHERE (((Products.NDC_Number)= 005915430)) AND (((Products.ProductName)= 'Allopurinol'))


I hope this is what you meant.
Kelly

 

[glalsop]

The SQL strings look good... I assumed that NDC_Number was stored as a number field. Is it a text field that displays a number?

If so, then change this:

If Not IsNull(Me.NDC_Number) Then
   sSQL = sSQL & "(((Products.NDC_Number)= " & [Forms]![QBF_Form]![NDC_Number] & ")) AND "
Else
   sSQL = sSQL & " (1=1) AND "
End If

to this:

If Not IsNull(Me.NDC_Number) Then
   sSQL = sSQL & "(((Products.NDC_Number)= '" & [Forms]![QBF_Form]![NDC_Number] & "')) AND "
Else
   sSQL = sSQL & " (1=1) AND "
End If

and then see what happens...

Good luck



-Gary

 

[KellyNewUser]

That was great. I no longer get an error message. Now the only problem is that nothing shows up in the resulting browse_products form. Any idea why? You have been awesome with all this.

Thanks,
Kelly

 

[glalsop]

Of the 3 scenarios that we looked at earlier, which ones are failing to populate the browse_products form?

The SQL statements look okay, but to make sure do this:

Run the same three scenarios you did earlier, and for each situation cut the SQL statement from the immediate window. Then open a new query and switch to SQL view. Paste the SQL statement and then run it. Then post back whether any of them return data. If not, then we need to look at the SQL statements. If you do get results then the SQL statements are okay and there is something else wrong.

-Gary

 

[KellyNewUser]

When both of the fields were left blank it returned all possible results, as it should. When both fields were filled in it returned nothing and when only NDC_Number was filled in it returned nothing.

Kelly

 

[glalsop]

It seems that the NDC_Number is causing you problems. If you leave NDC_Number blank and only select 'Allopurinol' from the ProductName field then do you get the results that you would expect?

If so, then you need to bear in mind that since NDC_Number is a text field, the way in which it is entered on the form must be EXACTLY the same as it is in the table, including leading 0's, etc.

If the table has 0005915430, but you enter 005915430, then you will not get any records.

Try opening the Products table and ensuring that what you are entering on the form is identical to what is entered in the table.

Good luck

-Gary

 

[KellyNewUser]

Okay, I have gotten each of the NDC_Number and the ProductName to produce the correct results if they are run individually in a new query.(ie NDC_Number has a value but ProductName is blank) The problem is when they both have values in their fields. Nothing is produced for the results. Also the are still not passing the results to the browse_products form. Any ideas?

Kelly

 

[glalsop]

I am not sure why you are getting inconsistent results... One quick question -

Why do you need to search for the ProductName and the NDC_Number at the same time? Shouldn't it just be one or the other?

-Gary

 

[KellyNewUser]

The purpose of the database is for all the employees of a pharmacuetical company to be able to search through all the drugs we produce that have similiar characteristics. I use the ndc number and productname as example fields. But there will also be characteristics such as shape of the pill, color of the pill, date of fda approval, location of production, etc. So if I need to search for all the white round pills we produce, I can find all of them. Does this make any more sense?

Kelly