WHERE AND OR

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tamal" wrote in message
...
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

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:

((bill.frm_dt)=#01/01/1995#)) OR ((bill.frm_dt)=#01/01/1986#));

Bob
Tamal wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1