Modify Format of Date Query

[Guest]

Sir/Ma'am,
I have a query that searches out specific records by date. The query
works the only problem that I am having is when I run the query and it
prompts me for the date, it will only accept dates such as 22-Sep-81 or
09-22-81. The date that I need it to accept would be 19810922 or 1981-09-22,
and I can get to the point where I can make the date appear in that format
but I still can't run the query using that format. Any suggestions would be
greatly appreciated.

V/R
Leroy

 

[Rick B]

Not sure what you mean. The prompt asks you to enter a date. Why would you
want to enter "19810922"? That is not a date, it is a string of numbers.

The entry should accept any valid DATE format that is either in the US
format of MM/DD/YY or any non-ambiguous format such as DD-MMM-YY.

If you want the user to enter that string you mentioned, then you could use
some of the functions available in Access to turn it into a valid date and
use it in the query, but that seems like an awful lot of trouble.

 

[Guest]

Sir,
Unfortunately, it is the way the military has the numbers printed. They
prefer to have it like YYYYMMDD which for Sept 22 1981 would be 19810922. I
will look through the functions and see if I can't find something that will
help resolve that date. I know of certain functions that will allow you to
read certain characters first so I am thinking I could read the 5th through
the 8th characters first the do the 1st through the 4th characters like you
do in an excel spreadsheet. I'll respond what I discover.

V/R
Leroy

 

[Rick B]

Okay I don't understand. Are your users entering the criteria, or is it
pulling from a form?

What is your query? Post it here. If you are pulling the date from a field
in a form, then you can use the format command to modify it.

 

[Guest]

The users are entering the criteria after they click the button on the form
which runs the query and then prompts them for the information, which the
user would then enter the value as YYYYMMDD as military protocol states.
Unfortunately I can't prevent that, and so I need to work with a value as I
stated earlier as 19810922.
I am attaching a copy of the SQL code, but please understand that I mostly
use the wizard so I am kind of slow when it comes to the SQL code side of it.
The query essentially asks for a date, and searches for any records on that
dates between 5:00 am and 11:59 pm, and then also searches for that "date" +
1 between 12:01 AM and 04:59 AM. The reason for that is because the duty day
starts at 5:00 am and doesn't end til 5:00 am the next day so all records are
relevant. That is probably irrelevant I just hope it helps you understand the
code a little better.

V/R
Leroy


--- Beginning of Code ---


PARAMETERS [What date would you like?] DateTime;
SELECT TblExtras.*, TblBlotter.*
FROM TblBlotter LEFT JOIN TblExtras ON TblBlotter.BlotterID =
TblExtras.BlotterID
WHERE (((TblBlotter.Date)=[What date would you like?]) AND
((TblBlotter.Time) Between #12/30/1899 5:0:0# And #12/30/1899 23:59:0#)) OR
(((TblBlotter.Date)=[What date would you like?]+1) AND ((TblBlotter.Time)
Between #12/30/1899 0:1:0# And #12/30/1899 4:59:0#))
ORDER BY TblBlotter.Date DESC , TblBlotter.Time DESC;



--- End of Code ---

 

[Rick B]

If it were me, I'd make my prompt

[Enter desired date as mm/dd/yy]

maybe someone else will have a better suggestion. If they use other office
products or websites, they have to enter a date in a normal format. why
should your software be any different?

--
Rick B

The users are entering the criteria after they click the button on the form
which runs the query and then prompts them for the information, which the
user would then enter the value as YYYYMMDD as military protocol states.
Unfortunately I can't prevent that, and so I need to work with a value as I
stated earlier as 19810922.
I am attaching a copy of the SQL code, but please understand that I mostly
use the wizard so I am kind of slow when it comes to the SQL code side of it.
The query essentially asks for a date, and searches for any records on that
dates between 5:00 am and 11:59 pm, and then also searches for that "date" +
1 between 12:01 AM and 04:59 AM. The reason for that is because the duty day
starts at 5:00 am and doesn't end til 5:00 am the next day so all records are
relevant. That is probably irrelevant I just hope it helps you understand the
code a little better.

V/R
Leroy


--- Beginning of Code ---


PARAMETERS [What date would you like?] DateTime;
SELECT TblExtras.*, TblBlotter.*
FROM TblBlotter LEFT JOIN TblExtras ON TblBlotter.BlotterID =
TblExtras.BlotterID
WHERE (((TblBlotter.Date)=[What date would you like?]) AND
((TblBlotter.Time) Between #12/30/1899 5:0:0# And #12/30/1899 23:59:0#)) OR
(((TblBlotter.Date)=[What date would you like?]+1) AND ((TblBlotter.Time)
Between #12/30/1899 0:1:0# And #12/30/1899 4:59:0#))
ORDER BY TblBlotter.Date DESC , TblBlotter.Time DESC;



--- End of Code ---

Okay I don't understand. Are your users entering the criteria, or is it
pulling from a form?

What is your query? Post it here. If you are pulling the date from a field
in a form, then you can use the format command to modify it.

--
Rick B



19810922.
I like
you would
you could
use date
and The
query 22-Sep-81
or that
format

 

[Guest]

TRy this for criteria --
DateSerial(Left([Enter date as YYYYMMDD],4),Right(Left([Enter date as
YYYYMMDD],6),2),Right([Enter date as YYYYMMDD],2))

 

[John Spencer (MVP)]

You could either parse it and use the dateserial function or you can use the
format function. To use this you will have to redefined the parameter as a
string (or leave it undefined). This will error if an improper string is input.
Much better off using a form to get the data - you can check it for correctness
before attempting to use it in a query.

CDate(Format([Input Date yyyymmdd,"@@@@\-@@\-@@"))

If it were me, I'd make my prompt

[Enter desired date as mm/dd/yy]

maybe someone else will have a better suggestion. If they use other office
products or websites, they have to enter a date in a normal format. why
should your software be any different?

--
Rick B

The users are entering the criteria after they click the button on the form
which runs the query and then prompts them for the information, which the
user would then enter the value as YYYYMMDD as military protocol states.
Unfortunately I can't prevent that, and so I need to work with a value as I
stated earlier as 19810922.
I am attaching a copy of the SQL code, but please understand that I mostly
use the wizard so I am kind of slow when it comes to the SQL code side of it.
The query essentially asks for a date, and searches for any records on that
dates between 5:00 am and 11:59 pm, and then also searches for that "date" +
1 between 12:01 AM and 04:59 AM. The reason for that is because the duty day
starts at 5:00 am and doesn't end til 5:00 am the next day so all records are
relevant. That is probably irrelevant I just hope it helps you understand the
code a little better.

V/R
Leroy


--- Beginning of Code ---


PARAMETERS [What date would you like?] DateTime;
SELECT TblExtras.*, TblBlotter.*
FROM TblBlotter LEFT JOIN TblExtras ON TblBlotter.BlotterID =
TblExtras.BlotterID
WHERE (((TblBlotter.Date)=[What date would you like?]) AND
((TblBlotter.Time) Between #12/30/1899 5:0:0# And #12/30/1899 23:59:0#)) OR
(((TblBlotter.Date)=[What date would you like?]+1) AND ((TblBlotter.Time)
Between #12/30/1899 0:1:0# And #12/30/1899 4:59:0#))
ORDER BY TblBlotter.Date DESC , TblBlotter.Time DESC;



--- End of Code ---

Okay I don't understand. Are your users entering the criteria, or is it
pulling from a form?

What is your query? Post it here. If you are pulling the date from a field
in a form, then you can use the format command to modify it.

--
Rick B



Sir,
Unfortunately, it is the way the military has the numbers printed.
They
prefer to have it like YYYYMMDD which for Sept 22 1981 would be 19810922.
I
will look through the functions and see if I can't find something that
will
help resolve that date. I know of certain functions that will allow you to
read certain characters first so I am thinking I could read the 5th
through
the 8th characters first the do the 1st through the 4th characters like
you
do in an excel spreadsheet. I'll respond what I discover.

V/R
Leroy

:

Not sure what you mean. The prompt asks you to enter a date. Why would
you
want to enter "19810922"? That is not a date, it is a string of
numbers.

The entry should accept any valid DATE format that is either in the US
format of MM/DD/YY or any non-ambiguous format such as DD-MMM-YY.

If you want the user to enter that string you mentioned, then you could
use
some of the functions available in Access to turn it into a valid date
and
use it in the query, but that seems like an awful lot of trouble.

--
Rick B



Sir/Ma'am,
I have a query that searches out specific records by date. The
query
works the only problem that I am having is when I run the query and it
prompts me for the date, it will only accept dates such as 22-Sep-81
or
09-22-81. The date that I need it to accept would be 19810922 or
1981-09-22,
and I can get to the point where I can make the date appear in that
format
but I still can't run the query using that format. Any suggestions
would
be
greatly appreciated.

V/R
Leroy