Text Date Field Query

[SFCA]

I have a date field set as text. The filed needs to be text inorder for
other processes to work. The data is entered in the format of 19750521,
this is equivilent to YYYYMMDD. I am trying to write an anniversary query.
In other words I need the query to pull the records (using the example above)
for the current month. For example, regarless of the first 4 digits (year) I
need the query to show all records for 0501 thru 0531 as an example. I want
this criteria to use manually entered parameters, i.e. [Enter Beginning
Date], [Enter Ending Date].

 

[Allen Browne]

In the Field row of your query, enter:
DateSerial(Left([d],4), Mid([d],5,2), Right([d],2)
where d represents the name of your field.

Now you have a date field, you can ask for the dates in the Criteria row.

 

[Ofer]

You can try something like

Where Val(Right([FieldName],4)) Between Val([Please enter start month and
Day mmdd]) And Val([Please enter End month and Day mmdd])

Or
Where Val(mid([FieldName],3,2)) = Val([Please enter month to be filter on])

===============
I hope it make sense, there is no point of making the user to enter a full
date if you are not filtering on the year

 

[MGFoster]

I have a date field set as text. The filed needs to be text inorder for
other processes to work. The data is entered in the format of 19750521,
this is equivilent to YYYYMMDD. I am trying to write an anniversary query.
In other words I need the query to pull the records (using the example above)
for the current month. For example, regarless of the first 4 digits (year) I
need the query to show all records for 0501 thru 0531 as an example. I want
this criteria to use manually entered parameters, i.e. [Enter Beginning
Date], [Enter Ending Date].

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

To add to the suggestions:

CDate(Format([date column],"####-##-##"))

will convert the text value into a date value.

To convert a parameter into your text-date format:

Format([Enter Beginning Date],"YYYYMMDD")

If the user enters the parameter in a valid date format the result of
the Format() function will be the date in your stored format.

To get the month/day combination:

Format([Enter Beginning Date], "MMDD")

If you are just geting full month data all you have to ask for is the
month number:

[Enter the Month number (1-12)]

Using that in a query:

PARAMETERS [Enter the Month number (1-12)] Byte;
SELECT *
FROM table_name
WHERE Mid(date_column,5,2) =
Format([Enter the Month number (1-12)],"00")

This will be a relatively slow query 'cuz you have to convert the
date_column to just the month number. This requires the query optimizer
to scan the whole table instead of just those dates that are relevant
(if you had the column indexed & a DateTime data type).

It's always better to store dates as DateTime data types, which you can
later change to display in whatever format your user's expect, but
keeping the underlying data type as a DateTime.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRDVOtoechKqOuFEgEQLBFwCgpe/lIUo2EmUoHlhTQUPGnrLAU6EAnjAz
XWYA4QjOSH5LCgBz+Hcc4Zcd
=Qy4g
-----END PGP SIGNATURE-----