Searching for Part of a Date

[Ice Rhino]

I have a date field that contains the date in a format dd/mm/yyyy. I wish to write a query that only searches for mm/yyyy irelevant of the dd value. I can't change the format of the src table as there are other queries that require use of the full date value.

Could some provide a simple syntax that will allow me to search for part of a date please.

Regards

 

[CBragg]

Try this.

=Format(Date(),"mm/yyyy")

 

[Mile-O]

Make a calculated field in the query based on the date field:


SELECT Format([DateField], "mm/yyyy") AS QueryDate
FROM MyTable
WHERE Format([DateField], "mm/yyyy") = yourCriteria;

 

[Ice Rhino]

My sincerest thanks to you both

Regards

 

[Mike375]

I have a date field that contains the date in a format dd/mm/yyyy. I wish to write a query that only searches for mm/yyyy irelevant of the dd value. I can't change the format of the src table as there are other queries that require use of the full date value.

Could some provide a simple syntax that will allow me to search for part of a date please.

Regards

This has always worked me in on the criteria row

Like "*/09/*52"

 

[Mile-O]

This has always worked me in on the criteria row

Like "*/09/*52"

Are your dates actually text? Also, it's not very dynamic.

 

[Mike375]

Are your dates actually text? Also, it's not very dynamic.

Date field, not text field.

 

[Mile-O]

It's converted to text though. If it was a date you'd have to use the # delimiters and wildcards can't be used with a date value.

 

[Mike375]

It's converted to text though. If it was a date you'd have to use the # delimiters and wildcards can't be used with a date value.

I don't know what you mean by its converted to text.

The field is a date/time field. When the query opens you can sort and it sorts as a "date"

If I enter a date in the criteria as 23/5/78 then Access changes it to #23/05/1978#

Mike

 

[Mile-O]

If I enter a date in the criteria as 23/5/78 then Access changes it to #23/05/1978#

Yes.

#23/05/1978# is a Date
"23/05/1978" is Text

Like "*/09/*52" is a wildcard expression on Text.

 

[Mike375]

Yes.

#23/05/1978# is a Date
"23/05/1978" is Text

Like "*/09/*52" is a wildcard expression on Text.

While I have your attention, if you get a moment have a look at my post in General on Need Lateral Thinking and my last post on using Len. Can you think of anything to add or negatives etc. So far it is the only solution I can come up with.

Mike

 

[Pat Hartman]

This is the warning from help regarding the use of wildcards with non-text fields:
Wildcard characters are meant to be used with fields that have the Text data type. You can sometimes use them successfully with other data types, such as dates, if you don't use the Microsoft Windows Control Panel to change the regional settings for these data types.

 

[mtairhead]

Regional Settings

Wildcard characters are meant to be used with fields that have the Text data type. You can sometimes use them successfully with other data types, such as dates, if you don't use the Microsoft Windows Control Panel to change the regional settings for these data types.

If regional settings for these datatypes have been changed, what am I looking for to change them back? How do you change these regional settings in the first place?

Thanks,

Andrew

 

[Pat Hartman]

Regional settings are changed via the Windows control panel.

The point is DO NOT use string functions on numeric fields. There are plenty of date functions to extract parts of a date. You can also use the Format() function if necessary.