473,322 Members | 1,401 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Access Date Format Input

2
Hi, I've searched the forum for an answer to my problem. I found an archived thread with the same problem but unfortunatly it's solution isn't very clear. The archived thread is here :

http://www.thescripts.com/forum/thread204990.html

I have a query with the fields [DateWorked] containing short dates, [StaffPIN] containing long ints, and a calculated field:

Hours: WorkedHours([DateWorked], [StaffPIN])

The function WorkedHours() is a user defined function. It returns the number of hours a staff member worked on a day, or an abbreviation of why they didn't work. It works fine in the intermediate window of the VB editor when I type:

?WorkedHours(#01/11/07#,12177)

And it works if I type (the point of giving two examples will be explained later):

?WorkedHours(#21/11/07#,12177)

The formats of the above dates is dd/mm/yy, the same as my (UK) system setting (I have checked).

In the query, however, it only works on dates where the day value is larger than twelve. When the day is less than twelve, the query seems to pass the date to WorkedHours() in the format yy/dd/mm. WorkedHours is expecting it in the dd/mm/yy format.

Hope that made sense!
How can I solve this?
Nov 29 '07 #1
3 4359
MikeTheBike
639 Expert 512MB
Hi, I've searched the forum for an answer to my problem. I found an archived thread with the same problem but unfortunatly it's solution isn't very clear. The archived thread is here :

http://www.thescripts.com/forum/thread204990.html

I have a query with the fields [DateWorked] containing short dates, [StaffPIN] containing long ints, and a calculated field:

Hours: WorkedHours([DateWorked], [StaffPIN])

The function WorkedHours() is a user defined function. It returns the number of hours a staff member worked on a day, or an abbreviation of why they didn't work. It works fine in the intermediate window of the VB editor when I type:

?WorkedHours(#01/11/07#,12177)

And it works if I type (the point of giving two examples will be explained later):

?WorkedHours(#21/11/07#,12177)

The formats of the above dates is dd/mm/yy, the same as my (UK) system setting (I have checked).

In the query, however, it only works on dates where the day value is larger than twelve. When the day is less than twelve, the query seems to pass the date to WorkedHours() in the format yy/dd/mm. WorkedHours is expecting it in the dd/mm/yy format.

Hope that made sense!
How can I solve this?
Hi

In the user defined function you will need to format the date as American date, ie.
Format(YourDateVariable,"mm/dd/yy")

Without seeing the code it is difficult to be more explicit.

VBA (and VB) only recognises/uses American dates, whereas the query designer will look at the local computer settings and adjusts accordingly.
Try typing a date in the criteria field for a date in query design grid, and then look at the SQL view !!

BTW, if you are returning many records, I find queries run faster if you can do the calculation in the query rather that a user defined function. This is normaly possible if the DB is stucture properly!!??


MTB
Nov 29 '07 #2
FishVal
2,653 Expert 2GB
Hi, everyone.

The problem is even worse.
VBA don't accepts dates in mdy format only, but also silently adjusts date which value doesn't fit mdy format.

Example (in VBA immediate window):

? Format(#27/11/2007#, "mmm-dd-yyyy")
Nov-27-2007

? Format(#7/11/2007#, "mmm-dd-yyyy")
Jul-11-2007

and to make you feel proud of it - the best
? Format(#27/11/7#, "mmm-dd-yyyy")
Nov-07-2027

Query builder adjusts dates in the same way.

P.S. Tested on Access 2003, Win XP2, Regional setting - american date format
Nov 29 '07 #3
JEA123
2
Hi

In the user defined function you will need to format the date as American date, ie.
Format(YourDateVariable,"mm/dd/yy")

Without seeing the code it is difficult to be more explicit.

VBA (and VB) only recognises/uses American dates, whereas the query designer will look at the local computer settings and adjusts accordingly.
Try typing a date in the criteria field for a date in query design grid, and then look at the SQL view !!
Works perfectly! Thanks! You don't know how much of a headache I've had with this. Your explanation (and FishVal's) solves other problem I've been having working with dates as well. Thank you both very much.

BTW, if you are returning many records, I find queries run faster if you can do the calculation in the query rather that a user defined function. This is normaly possible if the DB is stucture properly!!??
My database is not huge (user only enters a dozen or so values a day) so performance isn't high on my priorities. I use this function quite alot through out the db so it's more convenient to have a function. Thanks for the advice though, to me it's been invaluable.
Nov 29 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: Gerrit Holl | last post by:
Posted with permission from the author. I have some comments on this PEP, see the (coming) followup to this message. PEP: 321 Title: Date/Time Parsing and Formatting Version: $Revision: 1.3 $...
3
by: Jay | last post by:
I previously posted this question under Visual Basic newsgroup, but was advised to re-post here. I'm hoping someone can help me solve an issue I'm having with VB.Net and Access 2000. Here's...
4
by: Richard Hollenbeck | last post by:
I'm trying to write some code that will convert any of the most popular standard date formats twice in to something like "dd Mmm yyyy" (i.e. 08 Jan 1908) and compare the first with the second and...
14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
13
by: Peter James | last post by:
Access 97 If I select New on the Query tab of the db window, and go staight to sql view and type in the following for example: INSERT INTO tblMyTable ( dtDate, txtAny) VALUES (#2003-09-03#,...
9
by: Edward S | last post by:
I budget for a Project in an Excel sheet as illustrated below. The months below are usually a 2 year period i.e. 24 months, though it could be over 24 months depending upon a Project. I then...
4
by: rzhang | last post by:
Hi, We have an Access Application which works fine for most of the users. But there is one user who has the date input problem. When he enter a date field from a form, i.e. 09/03/2004 (Sept....
1
by: abcabcabc | last post by:
I write an application which can let user define own date format to input, How to convert the date string to date value with end-user defined date format? Example, User Defined Date Format as...
6
by: NH | last post by:
I want to allow users to enter dates in a text box in either the US "MM dd yy" format or the UK "dd MM yy" format. But how can I validate these dates? All the date functions e.g. ISdate,...
1
by: saddist | last post by:
Hello, I've been working on access 2000 for few days. I made some forms with textfields where you can type date. Those textfields had input mask set for short date 99/99/0000. Now software have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.