By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Using mulitiple fields to search a table

100+
P: 111
I have a form set up as a search page. The user needs to be able to search by 3 criteria: Group Name, Group Number, or CruiseLine AND SailDate. My problem is with CruiseLine AND SailDate. It works if i just use CruiseLine and it works if i just use SailDate, but i get type mismatch error when i use both! Here is my code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btndatesearch_Click()
  2. On Error GoTo Err_btndatesearch_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "frmMainGroup"
  8.  
  9.     stLinkCriteria = ("[SailDate]=" & "#" & Me![SailDate] & "#") And ("[CruiseLine]=" & "'" & Me![CrusieLine] & "'")
  10.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  11.  
  12. Exit_btndatesearch_Click:
  13.     Exit Sub
  14.  
  15. Err_btndatesearch_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_btndatesearch_Click
  18.  
  19. End Sub
  20.  
Aug 15 '07 #1
Share this Question
Share on Google+
15 Replies


Rabbit
Expert Mod 10K+
P: 12,389
I have a form set up as a search page. The user needs to be able to search by 3 criteria: Group Name, Group Number, or CruiseLine AND SailDate. My problem is with CruiseLine AND SailDate. It works if i just use CruiseLine and it works if i just use SailDate, but i get type mismatch error when i use both! Here is my code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btndatesearch_Click()
  2. On Error GoTo Err_btndatesearch_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "frmMainGroup"
  8.  
  9.     stLinkCriteria = ("[SailDate]=" & "#" & Me![SailDate] & "#") And ("[CruiseLine]=" & "'" & Me![CrusieLine] & "'")
  10.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  11.  
  12. Exit_btndatesearch_Click:
  13.     Exit Sub
  14.  
  15. Err_btndatesearch_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_btndatesearch_Click
  18.  
  19. End Sub
  20.  
Get rid of the paretheses. The logical operator 'And' needs to be within the quotes. You need to watch your spacing when concatenating strings.
Aug 15 '07 #2

100+
P: 111
that didn't work. Still a type mismatch error.
Aug 15 '07 #3

Rabbit
Expert Mod 10K+
P: 12,389
that didn't work. Still a type mismatch error.
What's it look like now?
Aug 15 '07 #4

100+
P: 111
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command57_Click()
  2. On Error GoTo Err_Command57_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "frmMainGroup"
  8.  
  9.     stLinkCriteria = "[SailDate]=" & "#" & Me![SailDate] & "#" And "[CruiseLine]=" & "'" & Me![CruiseLine] & "'"
  10.  
  11.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  12.  
  13. Exit_Command57_Click:
  14.     Exit Sub
  15.  
  16. Err_Command57_Click:
  17.     MsgBox Err.Description
  18.     Resume Exit_Command57_Click
  19.  
  20. End Sub


i thought the mismatch was occuring because SailDate is type Date and CruiseLine is Text, because they work exclusively. So i tried this...to no avail either....


Expand|Select|Wrap|Line Numbers
  1. Private Sub Command57_Click()
  2. On Error GoTo Err_Command57_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim SailDate As Date
  6.     Dim CruiseLine As String
  7.  
  8.     stDocName = "frmMainGroup"
  9.     Date1 = "[SailDate]=" & "#" & Me![SailDate] & "#"
  10.     Cruise1 = "[CruiseLine]=" & "'" & Me![CruiseLine] & "'"
  11.     stLinkCriteria = Date1 And Cruise1
  12.  
  13.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  14.  
  15. Exit_Command57_Click:
  16.     Exit Sub
  17.  
  18. Err_Command57_Click:
  19.     MsgBox Err.Description
  20.     Resume Exit_Command57_Click
  21.  
  22. End Sub

I'm not super skilled in VB so sorry for syntax errors - i guess thats why im here!
Aug 15 '07 #5

Rabbit
Expert Mod 10K+
P: 12,389
You removed the parentheses but you didn't do any of the other stuff I mentioned.
Aug 15 '07 #6

100+
P: 111
well i guess i dont understand. can you please show me the correct syntax?
Aug 15 '07 #7

Rabbit
Expert Mod 10K+
P: 12,389
Expand|Select|Wrap|Line Numbers
  1. "[SailDate]=#" & Me![SailDate] & "# And [CruiseLine]='" & Me![CruiseLine] & "'"
  2.  
Aug 15 '07 #8

100+
P: 111
thank you! That worked!
Aug 15 '07 #9

Rabbit
Expert Mod 10K+
P: 12,389
You're welcome.

But the real question is do you understand why it worked?
Aug 15 '07 #10

100+
P: 111
To be honest, no i do not. I'm pretty new to VB and dont understand all of the syntax. I just ordered a new book, so hopefully ill be able to learn more.
Aug 16 '07 #11

100+
P: 111
OK - new problem. I switched the SailDate text box to a combo box looking up the actual SailDate values from a table, thinking it wouldnt change anything. But now i get this error:

Syntax error in date in query expression '[SailDate]=# # And [CruiseLine]="'.

i havent changed my code but here it is again:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command57_Click()
  2. On Error GoTo Err_Command57_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "frmMainGroup"
  8.  
  9.     stLinkCriteria = "[SailDate]=#" & Me![SailDate] & "# And [CruiseLine]='" & Me![CruiseLine] & "'"
  10.  
  11.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  12.  
  13. Exit_Command57_Click:
  14.     Exit Sub
  15.  
  16. Err_Command57_Click:
  17.     MsgBox Err.Description
  18.     Resume Exit_Command57_Click
  19.  
  20. End Sub
Aug 16 '07 #12

Rabbit
Expert Mod 10K+
P: 12,389
"1+1=2" AND "2+2=5" -----> Data Type mismatch.
"1+1=2 AND 2+2=5" --------> "1+1=2 AND 2+2=5"

"Some" & "String" ----------> "SomeString"
"Some " & "String" ----------> "Some String"

This was what I meant by bringing the 'AND' inside the quotes and watching your spacing.
Aug 16 '07 #13

100+
P: 111
i think the problem is because when i choose a value from the combo box, its returning the (hidden) key column. I changed my BoundColumn to 2, thinking this would help, but it didnt. Any ideas?
Aug 16 '07 #14

100+
P: 111
Thank you for the concatenation explanation! :)
Aug 16 '07 #15

100+
P: 111
Nevermind! it was the bound column problem. it works now!
Aug 16 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.