Issue with Select Statement

[rywello]

I am writing a select statement to bring back the data for two plants 00BB and 01FT from the tblTrucks table, Plant column. The below query only brings back the data for 00BB but nothing for 01FT. Not sure what I am missing. Any help is appreciated.


strSQL = "SELECT * FROM tblTrucks WHERE [Plant] = '" & "00BB" & "'AND '" & "01FT" & "'"

Thanks,

 

[pbaldy]

For starters, why the unnecessary concatenation?

What is supposed to be equal to 01FT?

 

[rywello]

I got the first part of the select statement to work and bring back all the records for 00CX so I tried adding on to it.

strSQL = "SELECT * FROM tblTrucks WHERE [Plant] = '" & "00CX" & "'"

 

[rywello]

I want to bring back all the records for the plants 00CX and 01FT.

 

[sneuberg]

Maybe

strSQL = "SELECT * FROM tblTrucks WHERE [Plant] = '00BB' AND [Plant] = '01FT'"

is what you are looking for,i,e,, if Plant is supposed to equal '01FT'

 

[pbaldy]

Then you want OR, not AND, and you have to repeat the field name. Also, the concatenation just confuses things. I'd only use it when you were getting the value of a form control or variable. Try.

strSQL = "SELECT * FROM tblTrucks WHERE [Plant] = '00BB' OR [Plant] = '01FT'"

 

[pbaldy]

Maybe

strSQL = "SELECT * FROM tblTrucks WHERE [Plant] = '00BB' AND [Plant] = '01FT'"

is what you are looking for,i,e,, if Plant is supposed to equal '01FT'

Surprising if plant could be equal to 2 different values, and was I too slow?

 

[rywello]

That works great! Thanks!

 

[rywello]

I used the statement with the "OR" instead of "AND". Thanks again!

 

[pbaldy]

No problem.

 

[rywello]

Hi Again, I have tried adding on to my select statement but I am getting the error "Too few parameters. Expected 2. I am trying to pull the records that have a PIGIDATE equal to today's date or 1 day greater than today's date and the PlantAnticipatedDate has no value. Not sure where I am going wrong. Any assistance is appreciated.

strSQL = "SELECT * FROM tblTrucks WHERE [PIGIdate]<=Date or [PIGIdate]= DateAdd(day,1,Date) AND [PlantAnticipatedDate] Is Null AND [Plant] = '00BB' OR [Plant] = '01FT' ORDER BY tblTrucks.Plant ASC"

 

[sneuberg]

Unlike the literals such as '00BB' variables and functions in this case Date and DateAdd(day,1,Date) do need to be concatenated into the string with the appropriate delimiters. For dates this is the pound sign (#). I suggest you read over https://msdn.microsoft.com/en-us/library/dd627355(v=office.12).aspx and give it a shot.

 

[missinglinq]

Also, your syntax for DateAdd() is incorrect!

DateAdd(day, 1, Date)

should be

DateAdd("d", 1, Date)

Linq ;0)>

 

[pbaldy]

A couple of thoughts. When you mix AND & OR, you should clarify the logic with parentheses, or you may get unexpected results.

Also, your syntax for DateAdd() is incorrect![/B]

Unless the SQL is for a pass through query to SQL Server, in which case day is correct, with no quotes.