what went wrong ?? - DateValue Query

[Mann]

"SELECT DateBooked FROM DateCatalog WHERE (NOT(ISNULL
(DateBooked)) AND DateValue(DateBooked) >=1/1/2001)"

First, I am converting the DateBooked field using
DateValue function bcoz it is a Text Datatype.(I used
Text Datatype in my DateBooked field bcoz I found it hard
to Insert a NULL value if it's in Date DataType).

What's wrong with my Query, it doesn't return any single
record.

P.S.
If you could teach me how to insert a NULL value to a
Date DataType field, that will be a great help on me.

Thanks!

 

[John Vinson]

"SELECT DateBooked FROM DateCatalog WHERE (NOT(ISNULL
(DateBooked)) AND DateValue(DateBooked) >=1/1/2001)"

First, I am converting the DateBooked field using
DateValue function bcoz it is a Text Datatype.(I used
Text Datatype in my DateBooked field bcoz I found it hard
to Insert a NULL value if it's in Date DataType).

What's wrong with my Query, it doesn't return any single
record.

A Date query needs to be a) in American mm/dd/yy format (I can't tell
which you would use but it's worth the warning) and b) must be
delimited by # characters. Try

SELECT DateBooked FROM DateCatalog
WHERE DateValue IS NOT NULL
AND DateValue([DateBooked]) >= #1/1/2001#;

P.S.
If you could teach me how to insert a NULL value to a
Date DataType field, that will be a great help on me.

Ummm... just insert a NULL, or use a Form and don't type anything into
the textbox bound to the datefield. Use it all the time. What problem
are you having? How are you trying to do the insert?

 

[JohnFol]

Can I suggest a slight modification to the query as it's not explicityl US
format...??

SELECT DateBooked FROM DateCatalog WHERE DateValue IS NOT NULL
AND DateValue([DateBooked]) >= #1 Jan 2001#;

"SELECT DateBooked FROM DateCatalog WHERE (NOT(ISNULL
(DateBooked)) AND DateValue(DateBooked) >=1/1/2001)"

First, I am converting the DateBooked field using
DateValue function bcoz it is a Text Datatype.(I used
Text Datatype in my DateBooked field bcoz I found it hard
to Insert a NULL value if it's in Date DataType).

What's wrong with my Query, it doesn't return any single
record.

A Date query needs to be a) in American mm/dd/yy format (I can't tell
which you would use but it's worth the warning) and b) must be
delimited by # characters. Try

SELECT DateBooked FROM DateCatalog
WHERE DateValue IS NOT NULL
AND DateValue([DateBooked]) >= #1/1/2001#;

P.S.
If you could teach me how to insert a NULL value to a
Date DataType field, that will be a great help on me.

Ummm... just insert a NULL, or use a Form and don't type anything into
the textbox bound to the datefield. Use it all the time. What problem
are you having? How are you trying to do the insert?

 

[Michel Walsh]

Hi,


#mm-dd-yyyy # is interpreted as a US format, first (exception: in the query
designer in design view).

"Jan" would be more restricted, since it is only understood in English.


Hoping it may help,
Vanderghast, Access MVP

Can I suggest a slight modification to the query as it's not explicityl US
format...??

SELECT DateBooked FROM DateCatalog WHERE DateValue IS NOT NULL
AND DateValue([DateBooked]) >= #1 Jan 2001#;

"SELECT DateBooked FROM DateCatalog WHERE (NOT(ISNULL
(DateBooked)) AND DateValue(DateBooked) >=1/1/2001)"

First, I am converting the DateBooked field using
DateValue function bcoz it is a Text Datatype.(I used
Text Datatype in my DateBooked field bcoz I found it hard
to Insert a NULL value if it's in Date DataType).

What's wrong with my Query, it doesn't return any single
record.

A Date query needs to be a) in American mm/dd/yy format (I can't tell
which you would use but it's worth the warning) and b) must be
delimited by # characters. Try

SELECT DateBooked FROM DateCatalog
WHERE DateValue IS NOT NULL
AND DateValue([DateBooked]) >= #1/1/2001#;

P.S.
If you could teach me how to insert a NULL value to a
Date DataType field, that will be a great help on me.

Ummm... just insert a NULL, or use a Form and don't type anything into
the textbox bound to the datefield. Use it all the time. What problem
are you having? How are you trying to do the insert?

 

[JohnFol]

Agreed, however you should not rely on the tool interpreting what you meant.
For example if [DateBooked] in the example below was used as a forms
reference, you would not know how Access would react.

To show what I mean, one of the following MUST be wrong irrespective of
regional settings

? isdate(#13/01/2004#), isdate(#01/13/2004#)
True True

How as a developer can I check for a valid entry when Access is trying to
2nd guess me?

Hi,


#mm-dd-yyyy # is interpreted as a US format, first (exception: in the query
designer in design view).

"Jan" would be more restricted, since it is only understood in English.


Hoping it may help,
Vanderghast, Access MVP

Can I suggest a slight modification to the query as it's not explicityl US
format...??

SELECT DateBooked FROM DateCatalog WHERE DateValue IS NOT NULL
AND DateValue([DateBooked]) >= #1 Jan 2001#;

On Mon, 21 Jun 2004 18:56:11 -0700, "Mann"


"SELECT DateBooked FROM DateCatalog WHERE (NOT(ISNULL
(DateBooked)) AND DateValue(DateBooked) >=1/1/2001)"

First, I am converting the DateBooked field using
DateValue function bcoz it is a Text Datatype.(I used
Text Datatype in my DateBooked field bcoz I found it hard
to Insert a NULL value if it's in Date DataType).

What's wrong with my Query, it doesn't return any single
record.

A Date query needs to be a) in American mm/dd/yy format (I can't tell
which you would use but it's worth the warning) and b) must be
delimited by # characters. Try

SELECT DateBooked FROM DateCatalog
WHERE DateValue IS NOT NULL
AND DateValue([DateBooked]) >= #1/1/2001#;

P.S.
If you could teach me how to insert a NULL value to a
Date DataType field, that will be a great help on me.

Ummm... just insert a NULL, or use a Form and don't type anything into
the textbox bound to the datefield. Use it all the time. What problem
are you having? How are you trying to do the insert?

 

[Michel Walsh]

Hi,


#...# is FIRST tried as a US format, on any platform, but if it is not
right, such as if there is missing a number, it assumes the year is missing,
and it is this year, etc. That is part of OLEAUT32, and indeed, it tries
very hard to "make sense" of the data.


If you use a FORMS!FormName!ControlName reference, then you should NOT use #
at all, and use DoCmd (if possible), as in:


DoCmd.Execute "INSERT INTO ... WHERE f1=FORMS!FormName!ControlName "

Indeed, DoCmd solves, for you, the reference, and since it is a container,
not a constant, you don't need delimiter. Furthermore, the content of the
control is supplied by the end user in, hopefully, a format that is coherent
with the setting that same end user should have selected.


If as developer you have to embed a date constant, you should not relay on
the PC regional setting, but should force the US format:


str= "....WHERE f1=" & Format( someDate , "\#mm-dd-yyyy hh:nn:ss\#" )


and that would not create confusion by the computer, since #...# being FIRST
tried, that is what you supplied. On the contrary:


str= "... WHERE f1= #" & someDate & "#"


is ***NOT*** safe, since the conversion from date to string (which is
required to finalize the concatenation) is accordingly to the regional
setting, and so, dependant on the PC. That previous statement is very
unsafe, indeed, if your application has some potential of being
international, because the date_to_string is not aware of the # that will
surround it, and then, you may end up with dd-mm-yyy that is also a valid
mm-dd-yyyy and confusion would occur.


Forcing a US date, with # as delimiter, does not present that problem (and
don't need a specific language to be installed). And there is no second
guess then involved.



Vanderghast, Access MVP