On button click, what i'm trying to do is (in steps) - Return First Date of Current Month [New_Date] (Eg: 10/06/2008 would become 01/06/2008)
- Use [New_Date] in a query to pull out [tblPayMonths].[MONTH_ID]
- Reflect [MONTH_ID] in a field on a form called txtMonthID.
[tblPayMonths] contains the following sample data: - MONTH_ID SESS MONTH MONTH_START_DATE MONTH_END_DATE
-
07-JUN08 07 JUN 01/06/2008 30/06/2008
-
07-JUL08 07 JUL 01/07/2008 31/07/2008
-
08-AUG08 08 AUG 01/08/2008 31/08/2008
-
08-SEP08 08 SEP 01/09/2008 30/09/2008
I have the following code: -
Private Sub cmdTest_Click()
-
On Error GoTo Err_cmdTest_Click
-
-
Dim dbsCTrack As DAO.Database
-
Dim rstGenerateMonthID As DAO.Recordset
-
Dim sqlGenerateMonthID As String
-
-
Dim NewDate As Date
-
NewDate = DateSerial(Year(Date), Month(Date), 1)
-
Me.txtMonthID = NewDate
-
-
Set dbsCTrack = CurrentDb
-
sqlGenerateMonthID = "SELECT month_id FROM tblPayMonths WHERE (((month_start_date)=#" & NewDate & "#))"
-
Set rstGenerateMonthID = dbsCTrack.OpenRecordset(sqlGenerateMonthID)
-
-
MsgBox sqlGenerateMonthID
-
-
Me.txtMonthID = rstGenerateMonthID(1)
-
rstGenerateMonthID.Close
-
-
Exit_cmdTest_Click:
-
Exit Sub
-
-
Err_cmdTest_Click:
-
MsgBox Err.Description
-
Resume Exit_cmdTest_Click
-
-
End Sub
As you can see all dates are held in the table in the format dd/mm/yyyy.
However, when i try to query my table using the above SQL statement i am receiving the error message: "Item not found in this collection"
I have noticed when created as an access query with the parameter '01/06/2008' the SQL automatically produced swaps the date into US format like this..... - SELECT tblPayMonths.MONTH_ID FROM tblPayMonths WHERE (((tblPayMonths.MONTH_START_DATE)=#6/1/2008#));
.....so i believe this is where it is failing.
I'm really confused here about the formats, and why it would change back to US when all dates are stored as UK short dates. Can anyone suggest an alternative as i'm well and truely perplexed.
6 1576
I've just noticed that line 18 should have '0' in place of '1' and i have now changed it in my code. - Me.txtMonthID = rstGenerateMonthID(0)
Still none the wiser though. Please HELP!
patjones 931
Recognized Expert Contributor
Hi Lewe:
There are a couple of comments I have. I don't know that they will necessarily translate to solutions. First, what I noticed is that when I ran - DateSerial(Year(Date), Month(Date), 1)
in my Immediate Window, I got "6/1/2008" back. In other words, it gave me mm/dd/yyyy format, as you discovered when using the Query Builder to get Access' version of the SQL statement. Sometimes what I end up doing with dates is defining them as strings so that Access/VB does not impose any kind of automatic formatting on them.
Another thing you can try is to coax the date into the format you want by doing this: -
-
NewDate=Format(DateSerial(Year(Date), Month(Date), 1), "dd/mm/yyyy")
-
When I ran that in my Immediate Window, I got the date in the format that you are looking for.
Finally, I have a feeling that the error message you are getting might be referring to the way that you are trying to referece the recordset. Usually, when you try to make an assignment like what you are doing on line 18, you not only have to specify the record number, but also what field in the record you want, such as: - Me.txtMonthID = rstGenerateMonthID(1)!month_id
or - Me.txtMonthID = rstGenerateMonthID(1).month_id
If these ideas don't pan out, set a breakpoint in the code and step through it to determine exactly what line it's failing on.
Pat
Hi. In addition to Zepphead's comments, bear in mind that a date is not stored in a particular dd/mm/yy format at all; it is stored as a date/time value which has an integer component, representing the number of days since a reference date of 1 Jan 1900, and a decimal part which represents the time. The format controls how it is displayed, not how it is stored.
You are running into difficulties because the ANSI SQL standard uses the American format for date literals, regardless of the regional settings of your PC - hence mm/dd/yyyy in immediate window. You can always use Format to show the date in any other format you like - Format(somedate , "dd/mm/yyyy") will return the normal long-year UK form of the date somedate, for instance.
See also the following HowTo article by NeoPa on Literal Date Times and their Delimiters
-Stewart
Thanks guys. What i did to fix this was set the format to US date at the beginning. - NewDate = Format(DateSerial(Year(Date), Month(Date), 1), "mm/dd/yyyy")
America has left the world two legacies which get to me. One is Letter paper, as opposed to A4. The other mm/dd/yy dates. Personally, I format all dates to yyyy/mm/dd but use Cdate and # separators or other such devices whenever there may be inconsistency. One reason for using yyyy/mm/dd is that they can be sorted consistently as is.
One could write pages and pages on the topic, but the references given earlier are very relevant and immediately available
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Jerome |
last post by:
Hi,
I know this is an old problem and I've already tried to look the
solution up on the web but I didn't find what I need.
So, there's the following situation:
I've got an ASP page with a form, containing two fields that poses problems
1. birthday field
|
by: Lyn |
last post by:
Hi,
I am developing a project in which I am checking for records with
overlapping start/end dates. Record dates must not overlap date of birth,
date of death, be in the future, and must not overlap existing records from
the same table.
I had this all working some time ago, but recently when I have gone back to
do more testing, part of these validations no longer work. While there have
been changes to the code in the meantime, I cannot...
|
by: StBond |
last post by:
Hi everyone,
I am new to Access and Visual Basic so things my be getting across a
bit cloudy. I only started using VB for one week.
I am having a little problem with the database that I am working on. I
am working with MS Access 2002. And I am having a problem with one of
my charts.
I will explain how everything is laid out then go into details.
|
by: Andy_Khosravi |
last post by:
I have been trying to build a user friendly search engine for a small
database I have created. I'm having some particular problems with one
of my date fields.
Here's the setup:
I'm using Access 97 (I know it's old. But, it's the tool they
give me to work with)
My working knowledge of SQL is on the low side.
My working knowledge of VBA is beginner.
|
by: Geoff Jones |
last post by:
Hi
I have a table that has a column with Date types.
I am trying to view certain rows in the table using a DataView.
Using the filter, I can view the rows with, for example, the date equal to
something e.g.
vue.RowFilter = "Date = #01/01/03#"
| |
by: seegoon |
last post by:
Hi to all.
I have a small problem I hope someone can help me with.
I am running a sql query to a csv file. The query searches for the
total of a column between 2 dates.
This is a copy of one of the query's:
Dim SF_SMT As New OleDb.OleDbCommand("Select count(*) from
fault_records.csv where F4 = '" & lblprod.Text & " ' " & " AND F8 AND
F1 between #" & SelectproductForm.datestart & "# and #" & Now.Date &
|
by: hardik |
last post by:
hi friends,
i am really surprized the way access behaves in date fields i mean
it's all ok when you have us time zone or us servers but if you have
diffrent timezone like uk then access creates so many problems ....
first of all i try access with uk settings in date/time in field but
access changes date after 09/11 to 11/09 i don't know why so i decided
to take it as text field steel it is creating problems
|
by: Dave |
last post by:
Hello,
I have a possible problem exporting a text field that happens to
contain dates (but is not a date field) when using TransferText in MS
Access 2000.
I am exporting a query to a text file using TransferText in a module.
One of my fields is a text field called "invoice description". This
field may include any content including dates (e.g., 01/01/2006,
1/1/2006, 01/01/06, Invoice #1, Jan 2006, 293842, etc. are all
|
by: creative1 |
last post by:
Hello everyone.
I am experiencing a strange problem that I can't fix on my own. I think I need expert's suggestions for this.
The problem is:
I want to print account statement (or any other report) from VB form based on an entered date in the masked fields (dd/mm/yy). I am using one form to display five reports. I send date to display report after formatting it (m/dd/yy). When I enter date range like this in masked textbox (DD/MM/YY)
...
|
by: Ceebaby via AccessMonster.com |
last post by:
Hi Folks
I wondered if someone could point me in the right direction before I
completely tear my hair out.
I have a user selection form where options can be selected for a report.
Users now want to also filter the options by date selections or not if they
wish.
I added to unbound text fields to input the start and end dates and inserted
them into my str Where code. It was working fine until the these were added.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |