Date formating.

[Dan]

HI all, I have a form that I have a start and end date field that auto
generates. What I need now is the same start and end date that will auto
generate but will be in text format not date format, and will need to look
like yyyy-mm-dd so that I can have one of my queries ato pull this date from
my form. I don't know if this is possible. Let me know. Thanks

 

[Ken Snell MVP]

Why does it have to be in "text" format? Store the date as a DateTime date
type, and change the Format property of the textbox to "yyyy-mm-dd" so that
it displays the way you want.

Then you can use the field's value just as any other date value.

 

[Dan]

Ken, Thanks for the reply. The reason that I need it to be in text format is
because I am referencing this field in a couple of my Queries which are ODBC
links to a different system. The other system does not recognize the field in
date timestamp format it needs to be text, or he ODBC call will fail or bring
back zero records.

 

[Ken Snell MVP]

Are the "start date" and "end date" textboxes on your form bound to fields
in the form's RecordSource? What are the values that you want to
autogenerate for the "start date" and "end date"?

 

[Ken Snell MVP]

In addition to my other questions, are you using the textboxes directly as
parameter values for VBA code for the OBDC call? Show us some details about
what you're doing.

 

[Dan]

Ken the feilds I have in my Forms are unbound text boxes. And yes I am using
them in my query directly as parameter values. I guess If there was a way to
just put this formula directly into the query that would work as well. I am
just trying to automate the query so that it will pull Prior weeks data. So I
would have it run every Monday from prior sunday to prior saturday. I have
attached the current SQL of one of my queries that I am using. The feild
DACDT is the ceate date feild that I am trying to automate, which needs to be
pulled in a text yyyy-mm-dd format. Hope this helps.

SELECT [MA#FILECEN_MAPDAY].DACDT, Sum([MA#FILECEN_MAPDAY].DACOST) AS
SumOfDACOST
FROM [MA#FILECEN_MAPDAY]
GROUP BY [MA#FILECEN_MAPDAY].DACDT, [MA#FILECEN_MAPDAY].DAHOSP,
[MA#FILECEN_MAPDAY].DAOPT
HAVING ((([MA#FILECEN_MAPDAY].DACDT) Between [Forms]![Form1]![Start date]
And [Forms]![Form1]![End Date]) AND (([MA#FILECEN_MAPDAY].DAHOSP)=132) AND
(([MA#FILECEN_MAPDAY].DAOPT)=4))
ORDER BY [MA#FILECEN_MAPDAY].DACDT;

 

[Ken Snell MVP]

Try this query; it explicitly declares the data type of the parameters:

PARAMETERS [Forms]![Form1]![Start date] Date, [Forms]![Form1]![End date]
Date;
SELECT [MA#FILECEN_MAPDAY].DACDT, Sum([MA#FILECEN_MAPDAY].DACOST) AS
SumOfDACOST
FROM [MA#FILECEN_MAPDAY]
GROUP BY [MA#FILECEN_MAPDAY].DACDT, [MA#FILECEN_MAPDAY].DAHOSP,
[MA#FILECEN_MAPDAY].DAOPT
HAVING ((([MA#FILECEN_MAPDAY].DACDT) Between [Forms]![Form1]![Start date]
And [Forms]![Form1]![End Date]) AND (([MA#FILECEN_MAPDAY].DAHOSP)=132) AND
(([MA#FILECEN_MAPDAY].DAOPT)=4))
ORDER BY [MA#FILECEN_MAPDAY].DACDT;

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ken the feilds I have in my Forms are unbound text boxes. And yes I am
using
them in my query directly as parameter values. I guess If there was a way
to
just put this formula directly into the query that would work as well. I
am
just trying to automate the query so that it will pull Prior weeks data.
So I
would have it run every Monday from prior sunday to prior saturday. I have
attached the current SQL of one of my queries that I am using. The feild
DACDT is the ceate date feild that I am trying to automate, which needs to
be
pulled in a text yyyy-mm-dd format. Hope this helps.

SELECT [MA#FILECEN_MAPDAY].DACDT, Sum([MA#FILECEN_MAPDAY].DACOST) AS
SumOfDACOST
FROM [MA#FILECEN_MAPDAY]
GROUP BY [MA#FILECEN_MAPDAY].DACDT, [MA#FILECEN_MAPDAY].DAHOSP,
[MA#FILECEN_MAPDAY].DAOPT
HAVING ((([MA#FILECEN_MAPDAY].DACDT) Between [Forms]![Form1]![Start date]
And [Forms]![Form1]![End Date]) AND (([MA#FILECEN_MAPDAY].DAHOSP)=132) AND
(([MA#FILECEN_MAPDAY].DAOPT)=4))
ORDER BY [MA#FILECEN_MAPDAY].DACDT;

In addition to my other questions, are you using the textboxes directly
as
parameter values for VBA code for the OBDC call? Show us some details
about
what you're doing.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/