Solved Complex Query?

[Tieval]

Many thanks Minty and particularly CJ (for earlier help), is there any benefit to either of these methods over the one I am using?

 

[CJ_London]

speed - numeric comparisons are faster than text ones

 

[CJ_London]

In fact, because with true/false, 0 is false, any other number is true you could just have

WHERE scandate-int(scandate)

you don't need to include <>0 as another comparison - although I accept it probably makes the code easier to understand

to illustrate in the immediate window (now is today and includes time, date is just today)

?now()-int(now())<>0
True
?cbool(now()-int(now()))
True
?date()-int(date())<>0
False
?cbool(date()-int(date()))
False

 

[cheekybuddha]

>> Yes, I have that as my query is: <<

That's not what I posted.

It would be:

  WHERE Scanned > [Forms]![QMain]![qStartDate] AND Scanned < DateAdd("d",1,[Forms]![QMain]![qEndDate])

Note that WHERE Scanned > #2020-04-17# will not return 2020-04-17 00:00:00 but will return 2020-04-17 00:00:01 since it uses > rather than >=

 

[CJ_London]

@cheekybuddha if start date is 1st Jan and end date is 31st Jan and you have a record with a date, but no time of 20th Jan - how is that being excluded?

 

[cheekybuddha]

Re: speed

>> scandate-int(scandate) <<

Depending on the amount of records, you may find this is not so fast, since the condition is no longer sargable and the db engine won't be able to take advantage of using an index on that field.

hth,

d

 

[cheekybuddha]

@CJ,

Oops! Didn't spot that there are 2 date controls - just thought the query was looking to span a single day

 

[CJ_London]

the condition is no longer sargable and the db engine won't be able to take advantage of using an index on that field.

don't think it needs to - the index will have been applied to the scandate field to limit the returned records to those between start and end dates - so this is just an additional criteria - my example was not intended to be the whole criteria, just to illustrate you don't need the <>0 which cuts out another operation.

 

[cheekybuddha]

Absolutely!

I missed the point of your condition being in addition to the the date filtering condition.

Ignore all my chatter.