WHERE AND OR

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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:
 
Back
Top