Dange Range as a criteria (1 Viewer)

S

sneuberg

AWF VIP

Local time

Yesterday, 16:57

Joined

Oct 17, 2014

Messages

3,506

• Aug 11, 2016

• #21

fritz.panganiban said:

Using this code produces no record since the format in my date picker is mm/dd/yyyy

Click to expand...

That shouldn't make any difference as the format is applied to both the form text box and the field but try it without the format like

Code:Copy to clipboard

Where [dateField]>=[Forms]![frmDateRange]![txtFrom] And [dateField]<=[Forms]![frmDateRange]![txtTo]

But if this works it would be a surprise as it's the same as using Between

 

Last edited: Aug 11, 2016

S

sneuberg

AWF VIP

Local time

Yesterday, 16:57

Joined

Oct 17, 2014

Messages

3,506

• Aug 11, 2016

• #22

Please note my edit in my previous post it should have been

Where [dateField]>=[Forms]![frmDateRange]![txtFrom] And [dateField]<=[Forms]![frmDateRange]![txtTo]

 

Minty

AWF VIP

Local time

Today, 00:57

Joined

Jul 26, 2013

Messages

10,366

• Aug 11, 2016

• #23

Can you post up the whole SQL query you are using. There must be something else going on here.

 

Ranman256

Well-known member

Local time

Yesterday, 19:57

Joined

Apr 9, 2015

Messages

4,339

• Aug 11, 2016

• #24

IF you are using date and time in the field, then the end date may not work.
If you enter date only and it has time then it only includes items at midnight and nothing beyond.
If you want the entire end date then you must include all times 1/1/15 11:59:59 pm.
but this is only for End date. Start date starts a midnight and should not have a problem.

 

F

fritz.panganiban

Registered User.

Local time

Yesterday, 16:57

Joined

Jul 31, 2014

Messages

42

• Aug 12, 2016

• #25

arnelgp said:

how about this criteria:

Where Format([dateField],"yyyymmdd")>=Format([Forms]![frmDateRange]![txtFrom],"yyyymmdd") And Format([dateField],"yyyymmdd")<=Format([Forms]![frmDateRange]![txtTo],"yyyymmdd")

Click to expand...

Hi Arnel,

I replaced the SQL code to ">=" and "<=" to "Between" and "And", and now it works. You're the man

 

gemma-the-husky

Super Moderator

Staff member

Local time

Today, 00:57

Joined

Sep 12, 2006

Messages

15,634

• Aug 12, 2016

• #26

it shouldn't be an issue with US/UK date formats, but it is possible.

Generally SQL expects US dates, so where ambiguous a UK date is taken as an American date. 01/07/2016 can get treated as Jan 7th 2016, not 1st July. But using dates from controls should resolve this.


how are the controls formatted on your form? Are they set as shortdate format?

Between [Forms]![frmDateRange]![txtFrom] And [Forms]![frmDateRange]![txtTo]

Are users just typing a date in ,or picking from a date picker. Are users set to UK or US (or indeed some other) regional setting?