Date Format Problem . . .

[Rickinrsm]

My date field in my table is filled like thus:

2005-10-05 00:00:00

Is there a way to query "2005-10" and NOT include the 00:00:00 in the results.

Sort of a format instruction in the criteria portion of the query?

Thanks . . . Rick ~ Access Newbie

 

[PHV]

If the field is truly of type DateTime:
Format([your date field],'yyyy-mm')
If text type:
Left([your date field],7)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rickinrsm]

Thanks . . . this is waht I have

WHERE (((OCDownloadRES.CLOSEDDATE) Like "2005-10*")) And Format([OCDownloadRES.CLOSEDDATE],)"yyyymmdd");

I have some sort of syntax error, (missing oporater) problem.
It failed if I took out the And also.

What am I missing here?

Thanks . . . Rick Access Newbie

 

[PHV]

WHERE Format([OCDownloadRES].[CLOSEDDATE],'yyyy-mm') = '2005-10'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rickinrsm]

PHV . . . this returned the following:

2005-10-05 00:00:00

I'm looking for "yyyy-mm-dd" without any time referrences.

Thanks . . . Rick

 

[PHV]

In the query grid:[tt]
Field Date closed: Format([OCDownloadRES].[CLOSEDDATE],'yyyy-mm-dd')
Criteria Like '2005-10-*'[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rickinrsm]

Hot Damn . . . that worked GREAT!

In the Field it wanted Expr1: in front of the format but Access placed that there on it's own.

Thanks MUCH!!!

Rick

 

[Rickinrsm]

Now . . . is there a way in the query to make certain fields format to all UpperCase when the query executes?

Hope I'm not asking to much.

Thanks . . . Rick

 

[PHV]

Have a look at the UCase function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rickinrsm]

The Ucase seems to be a string function or data entry parameter. Isn't there a similar expression as you providede in your last?

I tried an Expression like this placed in the field portion of the [StName] in the query . . .

Expr1: Format([OcDownloadRES].[StName],'Uppercase')

The query runs but it does not change the values to all uppercase.

I'm not up to speed on the programing of reading and assigning strings yet. I wouldn't know where to begin.

Rick

 

[PHV]

Expr1: UCase([OcDownloadRES].[StName])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rickinrsm]

PH . . . this does the job perfectly. Kewl!

But for each Expr . . . in the table the query renames the field to the Expr1: Expr2: etc.

I have programs that use the field names. Will this create a problem because my program can no longer find [FieldName] because Expr3: replaced it?

Thanks much . . .

Rick ~ Access Newbie