SQL statement

[jnevada]

I need help with SQl statement. I only need the same order date with both clients. The following code doesn't work that way. It extracts all dates with c1 and c2



select OrderDate from tName
where CName= n1 or CName = n2 or CName = n3


Thanks a lot

[CreganTur]

Welcome to the forums- always good to see new members!

Unfortunately, you haven't provided near enough information for us to give you anything more than vague guesses.

For instance: are you executing this as a Query in Access, or are you executing the SQL via VBA? Are n1, n2, and n3 actual conditions you are trying to match, or are they VBA variables?

The following code doesn't work that way. It extracts all dates with c1 and c2
I have no idea what that means, as you are using n1, n2, and n3 in the code example you posted.

If you are doing this via VBA and n1, n2, and n3 are variables, then your SQL would need to look like this (I am also assuming that n1, n2, and n3 are date data type and that your CName field is setup correctly as Date data type):
Dim strSQL As String

strSQL = "SELECT OrderDate FROM tName " _
& "WHERE CName = #" & n1 & "# Or CName = #" & n2 & "# Or CName = #" & n3 & "#"

When you use VBA variables as part of a SQL string you must wrap them in special symbols to tell SQL what data type the variables are. For dates, use the pound sign (#). If you're using a string, then you repalce the pound sign with a single quote mark. If the Data Type is a number, then no symbol is used.

Also, SQL statements in VBA must be treated as strings and, as such, must be concatenated correctly with the variables. The underscore character above was added to keep the code from stretching long horizontally. It's a valid VBA construct that lets you split long lines of code over multiple lines for ease of reading.

If you are wanting to do this as a Query in Access (using the Queries object on the database window), then your code would look like this (assuming that n1, n2, and n3 are NOT variables- they are actual values in your table):
SELECT OrderDate FROM tName
WHERE CName = "n1" Or CName = "n2" Or CName = "n3";

I did not write in all of the parenthesis in the above SQL statement, it should work in Access without them.

HTH:thumb

[jnevada]

Welcome to the forums- always good to see new members!

Unfortunately, you haven't provided near enough information for us to give you anything more than vague guesses.

For instance: are you executing this as a Query in Access, or are you executing the SQL via VBA? Are n1, n2, and n3 actual conditions you are trying to match, or are they VBA variables?

I have no idea what that means, as you are using n1, n2, and n3 in the code example you posted.

If you are doing this via VBA and n1, n2, and n3 are variables, then your SQL would need to look like this (I am also assuming that n1, n2, and n3 are date data type and that your CName field is setup correctly as Date data type):
Dim strSQL As String

strSQL = "SELECT OrderDate FROM tName " _
& "WHERE CName = #" & n1 & "# Or CName = #" & n2 & "# Or CName = #" & n3 & "#"

When you use VBA variables as part of a SQL string you must wrap them in special symbols to tell SQL what data type the variables are. For dates, use the pound sign (#). If you're using a string, then you repalce the pound sign with a single quote mark. If the Data Type is a number, then no symbol is used.

Also, SQL statements in VBA must be treated as strings and, as such, must be concatenated correctly with the variables. The underscore character above was added to keep the code from stretching long horizontally. It's a valid VBA construct that lets you split long lines of code over multiple lines for ease of reading.

If you are wanting to do this as a Query in Access (using the Queries object on the database window), then your code would look like this (assuming that n1, n2, and n3 are NOT variables- they are actual values in your table):
SELECT OrderDate FROM tName
WHERE CName = "n1" Or CName = "n2" Or CName = "n3";

I did not write in all of the parenthesis in the above SQL statement, it should work in Access without them.

HTH:thumb

Gregan,

Thank you very much for your instruction on how to build SQL both in VBA and Access.

I think I didn't present my question clearly. I'm working on a SQL statement which will pull the order date for certain clients.

e.g client 1 has order date like 6/8/08 and 7/9/07. client 2 has order date like 6/8/08 and 5/1/07. I need to pull common date witch is 6/8/08 out. The SQL statement I have will pull three dates instead of the common date. Also if there is no common dates for certain clients, a message will pop-up.

Thanks again.