SQL - WHERE and AND

[Carl_R]

Where am I going wrong?

stSQL = stSQL & " WHERE (Year([ImplementationDate]))= " & CInt(txtSelectDate) And tblChanges.Group = ADEL

This is the bit that's killing me And tblChanges.Group = ADEL Without it the query works a treat except it pulls in all records based on the date range.

What I want is the report to show information based on the date AND the Group ADEL from the table tblChanges

Sorry but my SQL really stinks.

 

[The_Doc_Man]

Is it merely a typo or do you really mean


stSQL = stSQL & " WHERE (Year([ImplementationDate]))= " & CInt(txtSelectDate) & " And tblChanges.Group = " & C???(ADEL)

I added some quotes. I also don't know the format of ADEL so dont know what conversion might be needed. If it is raw text, you need extra quotes in a couple of places. Otherwise, you need a CStr, maybe.

 

[RichMorrison]

Carl,

The general form is:

WHERE( (condition 1) AND (condition2) )

In your case it would be:

WHERE
(
(Year([ImplementationDate]) = CInt(txtSelectDate) )
AND
(tblChanges.Group = ADEL)
)

I removed the quotes to emphasize the AND part. You do need to replace them in the string.

RichM

 

[Carl_R]

WHERE and AND

Hi

Thanks all for the help. Still having 'minor' probs.

I am at the stage where I input my date in my form (say 2002) but instead of the query running and pulling in all records for 2002 with a GROUP of ADEL, I get a box asking me for the word ADEL.

I enter the word ADEL in the box and hey-presto! My report is generated with the correct data....

...but how do I get this seamless, so ADEL doesn't have to be entered at all. ADEL is simply a text name in tblChanges.Group

Here's the code.

stSQL = stSQL & " WHERE ((Year([ImplementationDate]))= " & CInt(txtSelectDate) & ") And ((tblChanges.Group) = " & CStr("ADEL") & ")"

 

[Carl_R]

Re: WHERE and AND

After crying for my Mum and exhausting my supply of ciggies, something popped in that thing of mine I seldom call a brain.

Solution:

stSQL = stSQL & " WHERE ((Year([ImplementationDate]))= " & CInt(txtSelectDate) & ") And ((tblChanges.Group) = " & CStr(("'ADEL'") & ")")

Now my report is generated without that annoying parameter box.

I'm going for a beer
Cheers to those who replied. Certainly put me on the right track.

 

[simongallop]

If you are trying to get all records which have the word ADEL in the field 'Group' then all you need to do is:

stSQL = stSQL & " WHERE ((Year([ImplementationDate]))= " & CInt(txtSelectDate) & ") And ((tblChanges.Group) = 'ADEL')"

If though ADEL is a string variable that you have assigned a group name to earlier on in your code then you would need it to be:

stSQL = stSQL & " WHERE ((Year([ImplementationDate]))= " & CInt(txtSelectDate) & ") And ((tblChanges.Group) = '" & ADEL & "')"

HTH