Date format

[skilche1]

I am trying to find a date format and superised that MS doean't offer this feature. The format I am trying to set setup is this: 01/01/2006. Right now, all I can do is to set it up by the befault setting of 1/1/2006 and is not what I am looking for. Is there a way that this format can be modified?

Thanks,

Kilch

 

[boblarson]

Yes, you can specify your own format.

For example, in a query you could have
ShipDate:Format([ShipDate],"mm/dd/yyyy")

or in vba:
Me.YourTextBoxNameHere = Format(Me.YourTextBoxNameHere,"mm/dd/yyyy")

 

[Matt Greatorex]

For displaying the date, you could use Format(fieldname,"DD/MM/YYYY"). It will still be stored in the table as 1/1/2006, but look like 01/01/2006 to the user.

 

[skilche1]

Thanks guys, I have copied all the scripts and are on file.

bob

How could tack on "Format([DateID],"mm/dd/yyyy")
" onto "(Format([Production Date]-Weekday([Production Date])+2))"

Thanks again,

Kilch

 

[neileg]

For displaying the date, you could use Format(fieldname,"DD/MM/YYYY"). It will still be stored in the table as 1/1/2006, but look like 01/01/2006 to the user.

Actualy stored as a decimal number. The display in table view is a 'format' anyway.

 

[Matt Greatorex]

Actualy stored as a decimal number. The display in table view is a 'format' anyway.

You learn something new every day

 

[boblarson]

Format(([Production Date]-Weekday([Production Date]))+2),"mm/dd/yyyy")

But you should check into the DateAdd function for the calculation.

 

[skilche1]

Format(([Production Date]-Weekday([Production Date]))+2),"mm/dd/yyyy")

But you should check into the DateAdd function for the calculation.

bob, that worked ver well. Thanks again. Just wanted to up the code slightly in order to get it to work for others that may be looking for the same help:

Expr1: Format(([Production Date]-Weekday([Production Date])+2),"mm/dd/yyyy")

Had to remove the extra end parentheses ([Production Date]).

Thanks all.

 

[skilche1]

Ok, New Question

OK you guys. Here is another one. I am trying to setup a report by month. My issue is that its sorting alphabetically, not in calendar order. Anyone have any ideas?

Please view the attached Word doc for eamples of my issue.

It dispalys the Query setup, Query ran results and Report results.

Thnaks

Kilch

 

[boblarson]

In your query, create a column that sorts on your date and then make sure to set your grouping in your report to that same field or expression.

 

[skilche1]

In your query, create a column that sorts on your date and then make sure to set your grouping in your report to that same field or expression.

OK, think I have done that. Can you view the files I uploaded? It shows how its all set up. Thanks again bob.

 

[boblarson]

In your query, you sort on the 4th column over, but probably you are sorting based on the first column in the report sorting and grouping.

Go to View > Sorting/Grouping and make sure that the first sort listed is the one you actually want the report sorted by.