Sticky Query Problem

[grifter]

Hi All

I wonder if someone could suggest a workaround for this problem I am having with an update query. So the scenario is I am setting a value dependent on a date range. If a particular date is in current month range it sets the value "Y" if in outside the month range it sets it to "N". Now some of the dates are not in any range (before current month) and we have to leave that value as null/blank.

I set up a query with a bunch of IIf statements that worked seemingly quite well but on testing I noticed a data issue where at the turn of the month there was a date at end of month set to "Y", and another identical date too "N", this was not right as one date range is to check for being in the current month range and set to "Y".

I noticed that for some reason the dates I was comparing in the table also had a time stamp so comparing 31/04/2012 (using dateserial) against date in table (31/04/2012 07:00) this was seen as a date outwith the current month range check. My current month check was this:

IIf(Table.columnDate >= DateSerial(Year(Now), Month(Now), 1) AND Table.columnDate <= DateSerial(Year(Now), Month(Now) + 1, 0), "Y" , <other processing>)

So I used the DateValue function around my table.columnDate and so modified to this:

IIf(DateValue(Table.columnDate) >= DateSerial(Year(Now), Month(Now), 1)  AND DateValue(Table.columnDate) <= DateSerial(Year(Now), Month(Now) + 1, 0), "Y"  , <other processing>)

I then noticed I was getting an error for my returned result as an "#Error" instead of null where there was no date value to check.

I then decide I should put another IIf statement to set table.columnDate to null if the value was empty otherwise I would get this #error statement in my returned result due to DateValue itself being unable to handle nulls. So my statement then changed to:

IIf(IIf(Not IsDate(Table.columnDate, null, DateValue(Table.columnDate)) >= DateSerial(Year(Now), Month(Now), 1)  AND IIf(Not IsDate(Table.columnDate, null, DateValue(Table.columnDate)) <= DateSerial(Year(Now), Month(Now) + 1, 0), "Y"  , <other processing>)

As I had quite a lot if IIf statements in my query I then got the problem where it said the query size was too big for the query grid. Going back to the drawing board I had to rewrite the query I figured using SWITCH function but again I encounter exact same problem but this time cannot figure out how to check the value is a date or not then set to null if not a date, otherwise use datevalue of the column value to process the result. As far as I can see I am not sure how to use the following or a nested SWITCH if that exists in my current work:

IIf(Not IsDate(Table.columnDate, null, DateValue(Table.columnDate))

currently am spending far too much time on this and should have been finished a few days ago so any input would be a very big help to me in solving this last part or suggesting alternatives.

My previous code did work but just did not handle the values where there was no dates when I was using the DateValue function. A bit of a dilemma all the same.

Thanks

G

 

[spikepl]

If you change

Table.columnDate <= DateSerial(Year(Now), Month(Now) + 1, 0)

to

Table.columnDate [COLOR=Red]<[/COLOR] DateSerial(Year(Now), Month(Now) + 1, 0)[COLOR=Red]+1[/COLOR]

then you'll be good, whether or not the time component is in the date or not.

or even simpler,

Table.columnDate [COLOR=Red]<[/COLOR] DateSerial(Year(Now), Month(Now) + 1, [COLOR=Red]1[/COLOR])

Also, ask yourself why

DateValue(Table.columnDate) >= DateSerial(Year(Now), Month(Now), 1)

and

Table.columnDate >= DateSerial(Year(Now), Month(Now), 1)

will always give the same result (for non-Null dates, of course), thus rendering the DateValue function redundant in this comparison.

 

[grifter]

If you change

Table.columnDate <= DateSerial(Year(Now), Month(Now) + 1, 0)

to

Table.columnDate [COLOR=Red]<[/COLOR] DateSerial(Year(Now), Month(Now) + 1, 0)[COLOR=Red]+1[/COLOR]

then you'll be good, whether or not the time component is in the date or not.

or even simpler,

Table.columnDate [COLOR=Red]<[/COLOR] DateSerial(Year(Now), Month(Now) + 1, [COLOR=Red]1[/COLOR])

Also, ask yourself why

DateValue(Table.columnDate) >= DateSerial(Year(Now), Month(Now), 1)

and

Table.columnDate >= DateSerial(Year(Now), Month(Now), 1)

will always give the same result (for non-Null dates, of course), thus rendering the DateValue function redundant in this comparison.

Ok I see what you mean I was looking to utilise this datevalue function to effectively remove the time portion (when there was a date) so the returned result would not give spurious results when table.columnvalue and the end of month date were equal, but then using the +1 to move one day later from end of month calculation and only use < rather than <= seems like a good way to eliminate any spurious results due to the time portion.

Appreciate the suggestions cheers

G