• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 130
  • Last Modified:

2 criteria "then" statement pulling from Access

I am hving problem with below code:

If cboequipnum.Text = "Equipment 1" And cbomeslotnum.text = "A1" Then
Data1.RecordSource = "select * from main table where Meslot = 'A1' And Equipment# = 'Equipment1' "

main table is table in access db, and meslot and equipment# are fields of main table


I think I have the "select * "syntax wrong, as I get an some sort of date error when I try to excecute this code...If I remove the last line of the above code it will work.  Adding the and statement seems to throw it off.

Any suggestions
0
kevsh
Asked:
kevsh
  • 2
  • 2
1 Solution
 
manojaminCommented:
put [main table]
0
 
ryanvsCommented:
If "main table" is the table name with a space in it, change your query to [main table], such as:


select *
from [main table]
where Meslot = 'A1'
      And Equipment# = 'Equipment1'


I just reformatted it for readability, but it is the same thing.  If you are still having problems, but brackets [] around the field names as well.  If that doesn't work then also use double quotes, such as:

"select * " + _
"from [main table] " + _
"where [Meslot] = ""A1""" + _
     " And [Equipment#] = ""Equipment1"""

Good luck.
0
 
manojaminCommented:
i.e.


Data1.RecordSource = "select * from [main table] where Meslot = 'A1' And Equipment# = 'Equipment1' " 


also I hope that Equipment# and Meslot are the text fields...

0
 
ryanvsCommented:
If you want to be a little more elegant, you might also try the following:

Dim strSql As String

If cboequipnum.Text = "Equipment 1" And cbomeslotnum.Text = "A1" Then
  strSql = "select * " + _
           "from [main table] " + _
           "where [Meslot] = """ + cbomeslotnum.Text + """" + _
           "  And [Equipment#] = """ + cboequipnum.Text
End If

Data1.RecordSource = strSql


Good Luck.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now