WHERE AND OR

T

Tamal

Dear Sir
I have to select from the field EXCHNG_CODE containing GMV or MBH and frm_dt
contain 01/01/1986 or 01/01/1995.
I have written in the quary as follows:
WHERE (((bill.EXCHNG_CODE)="GMV") OR (((bill.EXCHNG_CODE)="MBH") AND
((bill.frm_dt)="01/01/1995")) OR ((bill.frm_dt)="01/01/1986"));
But error messege received "Data type mismatch in criteria expresion".
Please help.

Thanks

Tamal Das
 
A

Allen Browne

1. Open the Bill table in design view.
What is the Data Type of the frm_dt field?
If it is Date/Time, use # as delimiters around the value, e.g.:
WHERE (bill.EXCHNG_CODE = "GMV")
OR ((bill.EXCHNG_CODE = "MBH")
AND (bill.frm_dt = #01/01/1995#)
OR (bill.frm_dt = #"01/01/1986#);

2. If that still fails, what is the data type of EXCHNG_CODE?
If Number (typically because you have a lookup behind it), it will not match
the text values.

3. There is also a problem with the mixed AND/ORs.
You need to be aware that:
a AND (b OR c)
is not the same as:
(a AND b) OR c
You therefore need to indicate if you mean:
WHERE ((bill.EXCHNG_CODE = "GMV")
OR ((bill.EXCHNG_CODE = "MBH"))
AND ((bill.frm_dt = #01/01/1995#)
OR (bill.frm_dt = #"01/01/1986#));
or something else.
 
R

raskew via AccessMonster.com

Hi -
If bill.frm_dt is in date/time data format then dates should be surrounded
with #, not " and would cause a data type error. Example:
 
Top