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?
3 4359
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
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
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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 $...
|
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...
|
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...
|
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...
|
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#,...
|
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...
|
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....
|
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...
|
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,...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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
|
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...
| |