find yesterday

[thezinman]

can u pls help me???
How can I convert a date in an imported data file that is a text field type
to a date in a working data table that is a date field type, and write that
to the working data table that is dropped/re-created with every job run???
the imported data file date is a text field in the format of "mm/dd/yyyy"
i'm trying to use a query to create a working data table based on the date
of an imported data file in a text format.
the query drops & recreates the working data table with a date field in a
date format.
this query is run in a macro; so, i want the date to be yesterday, and
calculate yesterday automatically.
i tried creating a query that looks at the imported data file for the text
date, and reformats that to "yyyy/mm/dd".
then i calculate yesterday using date() - 1.
then i use this stryesterday as criteria of a second query to create the
working data table, but it's not working.

Thank you,
Mike

 

[Maurice]

You could write a function for that and use this in your query.

Try the following

In a module typ:

public Function ConvertDate (strField as String) as Date

dim strMonthPart as string
dim strDayPart as string
dim strYearPart as string

strMonthPart=mid(strField,2,2)
strDayPart=right(strfield,2)
strYearPart=left(strField,4)

ConvertDate=strYearPart & "-" &strMonthPart &"-"& strDayPart

end function

In your query you can create a field in the query grid as follows:
x:convertdate([yourfieldname])

This will create a datefield and in the criteria of that field you can place
date()-1

hth

 

[thezinman]

Maurice thank you so much for your help.
It almost works.
I'm struggling with whether the error i'm getting with this call is the fact
the data type passed into the function is a string, and should be a date, or
the data type returning is a date, and should be a string, or the fact I put
the date()-1 criteria into the query, and this data type does not match the
field data type for the function call, which is a string????
the error I'm getting is "data type mismatch in criteria expression.
Any ideas?
Thank you,
thezinman

You could write a function for that and use this in your query.

Try the following

In a module typ:

public Function ConvertDate (strField as String) as Date

dim strMonthPart as string
dim strDayPart as string
dim strYearPart as string

strMonthPart=mid(strField,2,2)
strDayPart=right(strfield,2)
strYearPart=left(strField,4)

ConvertDate=strYearPart & "-" &strMonthPart &"-"& strDayPart

end function

In your query you can create a field in the query grid as follows:
x:convertdate([yourfieldname])

This will create a datefield and in the criteria of that field you can place
date()-1

hth
--
Maurice Ausum

can u pls help me???
How can I convert a date in an imported data file that is a text field type
to a date in a working data table that is a date field type, and write that
to the working data table that is dropped/re-created with every job run???
the imported data file date is a text field in the format of "mm/dd/yyyy"
i'm trying to use a query to create a working data table based on the date
of an imported data file in a text format.
the query drops & recreates the working data table with a date field in a
date format.
this query is run in a macro; so, i want the date to be yesterday, and
calculate yesterday automatically.
i tried creating a query that looks at the imported data file for the text
date, and reformats that to "yyyy/mm/dd".
then i calculate yesterday using date() - 1.
then i use this stryesterday as criteria of a second query to create the
working data table, but it's not working.

Thank you,
Mike

 

[Maurice]

Ok, first of did you try running the query without the criteria set? Did that
work ok or not. If that doesn't return the proper set then we know that the
function needs some tuning. I passed your field as a string assuming your
datefield is a textfield. If it is already a date you should set it to date.

--
Maurice Ausum

Maurice thank you so much for your help.
It almost works.
I'm struggling with whether the error i'm getting with this call is the fact
the data type passed into the function is a string, and should be a date, or
the data type returning is a date, and should be a string, or the fact I put
the date()-1 criteria into the query, and this data type does not match the
field data type for the function call, which is a string????
the error I'm getting is "data type mismatch in criteria expression.
Any ideas?
Thank you,
thezinman

You could write a function for that and use this in your query.

Try the following

In a module typ:

public Function ConvertDate (strField as String) as Date

dim strMonthPart as string
dim strDayPart as string
dim strYearPart as string

strMonthPart=mid(strField,2,2)
strDayPart=right(strfield,2)
strYearPart=left(strField,4)

ConvertDate=strYearPart & "-" &strMonthPart &"-"& strDayPart

end function

In your query you can create a field in the query grid as follows:
x:convertdate([yourfieldname])

This will create a datefield and in the criteria of that field you can place
date()-1

hth
--
Maurice Ausum

can u pls help me???
How can I convert a date in an imported data file that is a text field type
to a date in a working data table that is a date field type, and write that
to the working data table that is dropped/re-created with every job run???
the imported data file date is a text field in the format of "mm/dd/yyyy"
i'm trying to use a query to create a working data table based on the date
of an imported data file in a text format.
the query drops & recreates the working data table with a date field in a
date format.
this query is run in a macro; so, i want the date to be yesterday, and
calculate yesterday automatically.
i tried creating a query that looks at the imported data file for the text
date, and reformats that to "yyyy/mm/dd".
then i calculate yesterday using date() - 1.
then i use this stryesterday as criteria of a second query to create the
working data table, but it's not working.

Thank you,
Mike