SQL used in VBA syntax 1

[Apollo13a]

Hi, I'm trying to use this SQL in VBA DAO command but I can't get the syntax right. Why do I have so many ""?

SQLsmt = "SELECT tblHistory.TicketNum, tblHistory.SignInDate FROM tblHistory WHERE TicketNum ="" '" & strThisMemberTicketNum & "' and SignInDate =  #" & ThisSignInDate & " #"""

Thanks. Jim

 

[BobAtHome]

I don't know why you have so many "" but I do know that you don't need as many as you have.

Try the following. Also since you are only selecting from 1 table, you don't need the table reference in front of the fields. The only time you need them is when you are using more than 1 table and there are fiekds with the same name in each table. Then the table reference is needed to select the field value from the right table.

SQLsmt = "SELECT TicketNum, SignInDate FROM tblHistory WHERE TicketNum = '" & strThisMemberTicketNum & "' and SignInDate = #" & ThisSignInDate & "#"

I am assuming strThisMemberTicketNum is a string.

If it is a number, you don't need the single quotes around strThisMemberTicketNum.

SQLsmt = "SELECT TicketNum, SignInDate FROM tblHistory WHERE TicketNum = " & strThisMemberTicketNum & " and SignInDate = #" & ThisSignInDate & "#

 

[BobAtHome]

I don't know why you have so many "" but I do know that you don't need as many as you have.

Try the following. Also since you are only selecting from 1 table, you don't need the table reference in front of the fields. The only time you need them is when you are using more than 1 table and there are fields with the same name in each table. Then the table reference is needed to select the field value from the right table.

SQLsmt = "SELECT TicketNum, SignInDate FROM tblHistory WHERE TicketNum = '" & strThisMemberTicketNum & "' and SignInDate = #" & ThisSignInDate & "#"

I am assuming strThisMemberTicketNum is a string.

If it is a number, you don't need the single quotes around strThisMemberTicketNum.

SQLsmt = "SELECT TicketNum, SignInDate FROM tblHistory WHERE TicketNum = " & strThisMemberTicketNum & " and SignInDate = #" & ThisSignInDate & "#