Set Date via VBA

I have a SQL INSERT statement that should insert a record into a table using 
valkues from a form. All values are going in OK apart from a date field, 
which is always being set to 30/12/1899 whether I set using the form field I 
really want or by setting a literal e.g. "11/11/2007". Can anyone please help 
me?
0
Utf
11/9/2007 6:26:02 PM
access 16762 articles. 3 followers. Follow

6 Replies
635 Views

Similar Articles

[PageSpeed] 4

/On Nov 9, 12:26 pm, Rose B <Ro...@discussions.microsoft.com> wrote:
> I have a SQL INSERT statement that should insert a record into a table using
> valkues from a form. All values are going in OK apart from a date field,
> which is always being set to 30/12/1899 whether I set using the form field I
> really want or by setting a literal e.g. "11/11/2007". Can anyone please help
> me?

If you want to enter a default value or assign the date value in code
you will need to use the
# sign arround your date.  As in #11/09/2007#.  Using quotes means it
is a string.

MAC

0
MAC
11/9/2007 6:33:30 PM
You need to delimit dates with # characters (and they need to be in a format 
that Jet will recognize, which generally means mm/dd/yyyy or yyyy-mm-dd. 
It's NOT dependent on the user's Regional Settings.).

I'm betting that your SQL string just has 11/11/2007 for the date, rather 
than #11/11/2007#. Without the delimiters, Jet actually treats it as 
division, resulting in a very small number ( .0004982561). Given that Access 
stores date/time values as eight-byte floating point numbers, where the 
integer portion represents the date as the number of days relative to 30 
Dec, 1899, and the decimal portion represents the time as a fraction of a 
day, that number is 00:00:43 on the morning of 30 Dec, 1899.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Rose B" <RoseB@discussions.microsoft.com> wrote in message 
news:55A2321D-1852-42DE-A60E-188BEC09C670@microsoft.com...
>I have a SQL INSERT statement that should insert a record into a table 
>using
> valkues from a form. All values are going in OK apart from a date field,
> which is always being set to 30/12/1899 whether I set using the form field 
> I
> really want or by setting a literal e.g. "11/11/2007". Can anyone please 
> help
> me? 


0
Douglas
11/9/2007 6:34:12 PM
Without seeing your SQL, this is just a guess of the most likely issue.

SQL statements need date literals to be surrounded by #. Examples:

"INSERT yada yada...#11/11/2007#..."
"INSERT yada yada...#" & Me.ControlWithDateValue & "#..."

-- 
HTH,
George


"Rose B" <RoseB@discussions.microsoft.com> wrote in message 
news:55A2321D-1852-42DE-A60E-188BEC09C670@microsoft.com...
>I have a SQL INSERT statement that should insert a record into a table 
>using
> valkues from a form. All values are going in OK apart from a date field,
> which is always being set to 30/12/1899 whether I set using the form field 
> I
> really want or by setting a literal e.g. "11/11/2007". Can anyone please 
> help
> me? 


0
George
11/9/2007 6:38:03 PM
Ideally I want to set it to a form value = e.g. Me.DateField, what would I 
need to do for this?

"MAC" wrote:

> /On Nov 9, 12:26 pm, Rose B <Ro...@discussions.microsoft.com> wrote:
> > I have a SQL INSERT statement that should insert a record into a table using
> > valkues from a form. All values are going in OK apart from a date field,
> > which is always being set to 30/12/1899 whether I set using the form field I
> > really want or by setting a literal e.g. "11/11/2007". Can anyone please help
> > me?
> 
> If you want to enter a default value or assign the date value in code
> you will need to use the
> # sign arround your date.  As in #11/09/2007#.  Using quotes means it
> is a string.
> 
> MAC
> 
> 
0
Utf
11/9/2007 6:43:03 PM
Nearly perfect!! I just now have the date (that starts out in dd/mm/yyyy) 
ending in mm/dd/yyyy in the table.

"George Nicholson" wrote:

> Without seeing your SQL, this is just a guess of the most likely issue.
> 
> SQL statements need date literals to be surrounded by #. Examples:
> 
> "INSERT yada yada...#11/11/2007#..."
> "INSERT yada yada...#" & Me.ControlWithDateValue & "#..."
> 
> -- 
> HTH,
> George
> 
> 
> "Rose B" <RoseB@discussions.microsoft.com> wrote in message 
> news:55A2321D-1852-42DE-A60E-188BEC09C670@microsoft.com...
> >I have a SQL INSERT statement that should insert a record into a table 
> >using
> > valkues from a form. All values are going in OK apart from a date field,
> > which is always being set to 30/12/1899 whether I set using the form field 
> > I
> > really want or by setting a literal e.g. "11/11/2007". Can anyone please 
> > help
> > me? 
> 
> 
> 
0
Utf
11/9/2007 6:50:01 PM
All working fine.....thanks!!!!!!

"George Nicholson" wrote:

> Without seeing your SQL, this is just a guess of the most likely issue.
> 
> SQL statements need date literals to be surrounded by #. Examples:
> 
> "INSERT yada yada...#11/11/2007#..."
> "INSERT yada yada...#" & Me.ControlWithDateValue & "#..."
> 
> -- 
> HTH,
> George
> 
> 
> "Rose B" <RoseB@discussions.microsoft.com> wrote in message 
> news:55A2321D-1852-42DE-A60E-188BEC09C670@microsoft.com...
> >I have a SQL INSERT statement that should insert a record into a table 
> >using
> > valkues from a form. All values are going in OK apart from a date field,
> > which is always being set to 30/12/1899 whether I set using the form field 
> > I
> > really want or by setting a literal e.g. "11/11/2007". Can anyone please 
> > help
> > me? 
> 
> 
> 
0
Utf
11/9/2007 6:57:01 PM
Reply:

Similar Artilces:

Minimum security settings
Hello. I am trying to set up a minimal security class for some of our users GP 9 SP2. Basically just enough to get the application loaded. This is due to a requirement in Field Services Anywhere for Technicians to have a GP login. I have tried using the document on the MS website which shows step-by-step how to do this, but when i limit it to what MS recommends, the user is unable to log in. Thanks, Neil Hi Neil You might find this document I wrote useful............... How to resolve Security errors on Login ======================================= Symptoms -------- When log...

Setting up Outlook as default email programme
Am currently running Outlook express and wish to switch to Microsoft Outlook. How is this done? Install Outlook/Office if you haven't done so already, and then in control panel, internet options, programs, set Outlook as default mail program. thicko wrote: > Am currently running Outlook express and wish to switch to > Microsoft Outlook. How is this done? ...

Entering Dates
How do I enter only the month and day in a cell and not have the year show up? You can format the cell Duffo Select the cell and do Ctrl-1 On the number tab choose date and choose the one you like(or make your own one) -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Duffo" <tpavey3@cox.net> wrote in message news:026401c38523$303f4bf0$a401280a@phx.gbl... > How do I enter only the month and day in a cell and not > have the year show up? I do that; I choose mm/dd and when I imput that's what I get, but, the year still shows up in the...

Blocking specific character sets with IMFv2
How can I block specific character sets (ie cyrillic or asian) with IMFv2? Thanks! That functionality is not provided with IMF (v1 or 2). Many 3rd party anti-spam apps do this. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Fritz" <fritz@dontbite.com> wrote in message news:e2mxTU5QGHA.4696@tk2msftngp13.phx.gbl... > How can I block specific character sets (ie cyrillic or asian) with IMFv2? > > Thanks! > Any idea if it'll be available in the next version of IMF? I mean, the idea of r...

Publisher 2002 Tab settings keep resetting to .5"
I want .25" tab settings in a Publisher 2002 file, but every time I reopen the file, the tab settings reset to .5". WHAT'S THE DEAL WITH THIS?? ...

Concatenate Text Fields to Date Field
I have three (3) columns in a table which represent the month, day, and year. The values in these columns are 04 (month) 21 (day) 2010 (year). I want to insert these thre values into another table where the field in the record is a Date/Time type. Here's the SQL statement (in part): INSERT INTO ....... SELECT [tblTable].[fMonth] & "/" & [tblTable].[fDay] & "/" & [tblTable].[fYear] AS fDate I get an error about this "append" that complains about "... set 1 fields(s) to Null due to type conversion failure, and it didn't a...

Setting ANR Include Online GAL per MSKB 831124
http://support.microsoft.com/default.aspx?scid=kb;en-us;831124&Product=out What is difficult for me to discern is this: The environment isn't using an Exchange Server. So, which of these are available / not available in this situation: Cached Exchange Mode / Cached Mode Automatic Name Resolution (ANR) Global Catalog Server Offline Address Book Global Address List In other words: How should ANR Include Online GAL be set if there's no Exchange Server? Thank you, Fred 1. no 2. not sure but I think yes. 3. no 4. no 5. no 6. you can't --� Milly Staples [MVP - Outlook] ...

how do i set up a list of customers in excell for mail merge
I am looking to set up a data base of 100 customers. I need to be able to pull the data from the spreed sheet to make lables and to do a mail merge letter. What would be the best way to set that up useing word and excell Create mailing labels by merging an address list Step 1: Create the main document Click New Blank Document on the Standard toolbar. On the Tools menu, click Mail Merge. Under Main document, click Create, and then click Mailing Labels. Click Active Window. The active document becomes the main document. Step 2: Open or create the data source In the Mail Merge Helper...

Custom Objects via the SDK
I've been hearing that it is possible to create custom CRM objects (in native format) via the SDK. Anyone does this and/or have pointers to direct me to the correct information? I've been doing SDK work via C# for a while, but have not seen this. Alex, it's not possible to "Create" custom CRM objects via the SDK. You can manually add them into the metabase and crm database yourself (not supported and will violate your license agreement), and the CRMQuery API will recognize them and let you query them, BUT the CRM API provides no way to update, delete or create thes...

VBA with excel worksheets?
I am new to VBA and am excel user. Can someone tell me: 1 - How to suck a range of excel data cells into vba code? I just want to put data into some cells and have VBA load that data into an array and use it then return the result into a specified cell in a spreadsheet like sheet1 in testfile.xls I thought i use Worksheets("Sheet1". Range Cells(A1:A6) .... But this syntax is not right and i am having trouble searching to find my solution to my problem. Thanks in advance, Andrew I suspect that yhis may be a case of my OE not showing me replies that you have had because I fin...

Can no longer send links or pages via email from IE
Hi all, I have tried to find information about a problem that has recently manifested itself on my XP Pro machine but either my googling skills are failing me or I'm just using the wrong search terms. When I File ... Send .... Link by email from IE 6, Outlook no longer constructs an email with a link but creates an attachment with names like att13B.tmp which I, possibly incorrectly, regard as useless. I have the same issue with 'page by email'. Does anyone know what causes this problem and if there's a fix ? I'm running Outlook 2002 SP3. I apologise if this is an Int...

How can I set Personal 'Public Holidays'
My wife's a teacher, so I'd like to add school holidays to my Outlook Calendar to appear the same as Public Holidays. Can I do this, if so how. Presumably I can create a file similar to UK Public Holidays and import/add this to my Outlook Calendar. -- Regards. Chris. Please remove MAPSON (NOSPAM backwards) to reply to me by email. See if this page has the information you need: http://www.slipstick.com/dev/olforms/holiday.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal accou...

Money 2005 release date
When is Money 2005 going to be released? Is there a web page yet that describes any new features or fixes? Some have said "Aug 1st" which is tomorrow, you'd think there would be some advance notice? There have been reports of some Retailers taking pre-release orders, but there has been no word from MS (as usual). -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.microsoft.com/mswish/suggestion.asp or for UK wishes http://www.microsoft.com/uk...

Date filling down
i want to fill a date down many cells but it fills weekends as well. is there way to fill down a date, excluding weekends? Enter your date in a cell. Select the cell, and *right click* and drag down to copy as needed. When you release the *right* mouse button, you see a dialog box with numerous options, one of which, is to fill the series with *weekdays*. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Stefano" <Stefano@discussions.microsoft.com> ...

Date format getting muddled
Hi, I work for a computer software company. We export data to Excel. We have a client in South Africa where the date is getting muddled when it is exported to Excel. For example, if the date read 19/11/2009, when exported to Excel it would read 2019/11/09 (as though it is changing the format from DD/MM/YYYY ti YYDD/MM/YY. I have tried changing the Regional settings and the language/date formats in Excel but this does not seem to recitfy the issue. Has anybody ever experienced this before? Any help would be gratefully recieved. (Just to confirm, this is not an issue...

VBA Editor skips lines and moves to other part of code
Hello, MS ACCESS 2003 on XP PRO. I have a form checkbox, and AFTERUPDATE of the value, I run a bunch of code. Part of the code calls a sub in a module. During this sub, I define two string values. (they happen to be shaped like SQL statements but at this point the computer doesn't know that) the first one runs fine, and the MSGBOX statement after shows the text correctly. However, when the yellow gets on top of the SECOND str_SQL = statement, and I hit F8, the code skips OUT of the SUB, back to the AFTERUPDATE statement on the line that would execute after the sub. T...

setting the cursor as a hand
I have overridden the CStatic class and created my own CStaticHover class. I want to make the mouse cursor to change to a hand and I would like the text to turn bold when the mouse hovers over any instance of my CStaticHover class. I can get the text to go bold when the mouse hovers, but can't get the cursor to change... I have captured the OnMouseMove and OnMouseLeave events and can successfully make the text bold when the mouse hovers over my object, (using _TrackMouseEvent), but I can't get the mouse cursor to change. I have tried doing a SetCursor at the same time as changi...

Date Formulas #3
How can I make a cell correct the year of a date Hi Charlene not really sure what you're after here if you have 1/1/05 and you want to display just 2005 then right mouse click on the cell and choose format cells / on the numbers tab, choose custom and then type yyyy in the white line and and click OK. however, this just displays the date showing only the year, if you want to extract the year only to another cell you can use =year(A1) where A1 contains the date if you're after something else, please type a few examples of the data you have and what you want to see. Cheers JulieD...

contract line date 09-08-05
Hi, I'm having a really weird problem. When i try to change the date on a contract line and save, i get an message from crm that de begin/end date is not correct. But it is! My date settings are dd/mm/ccyy. Thx Cypress, Your contract line start/end dates must fall on/within main contract start/end dates (Information > General Tab) or you'll get this message. Chuck "Cypress" wrote: > Hi, > > I'm having a really weird problem. When i try to change the date on a > contract line and save, i get an message from crm that de begin/end date is > not...

Using Date and time in YYYYMMDDHHMMSS format automatically
I have a question about using date and time in the following format: yyyymmddhhmmss, i.e. 20070328144801 I have a column in a table called ScLoadTime. I am trying to do an query which will automatically show me everything between yesterday at 17:00:01 and Today at 17:00:00 I am using the following: And ((ScLoadTime) Between Format(Date(),"yyyymmdd""170000""") And Format(DateAdd("d",-1,Date()),"yyyymmdd""170001""")) The problem is the query comes straight back with no results. If I changed the query to And ScLoadTime b...

Page Settings
Is there any way to copy page settings ie. Margins, landscape, headers from one sheet to the other without having to go in and individually change settings? I am using Excel 2003. Thanks! Hi if your 'master' sheet is the first sheet try the following: - select yiur 'master' sheet and the other sheets by grouping them (hold down the CTRL key while selecting them) - goto 'File - Pagesetuip' and simply click 'OK' Note: This copies ALL page settings -- Regards Frank Kabel Frankfurt, Germany Jane wrote: > Is there any way to copy page settings ie. Margins...

Date Filter
I have sales data for '05, listed by Customer in random order. Several customers have entries for '04 as well. I can sort by customer to make the list alphabetical. How do I filter the list to eliminate the '04 data? -- tech lead ------------------------------------------------------------------------ tech lead's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28862 View this thread: http://www.excelforum.com/showthread.php?threadid=486131 In the sorted by alpha list, do a Data > Filter > Autofilter on the dates col, Select "Custom&quo...

How do I set printing size?
I'm new to learning Publisher and are now doing the basic tutorials that come with it, mainly to find out all it does I didn't know it did. And, my usual way of jumping in and trying to do something, then figuring out how (all the ways it doesn't work) can get time-consuming and frustrating. I find myself up till 1am (late night it's easier to concentrate and I get involved in it) and thinking I LOVE THIS! (practicing setting up cards and newsletters and all the options) I have Publisher (OFFICE) 2000. I have an older computer and WIN 98 SE, and I don't think th...

VBA code to launch Email
I am looking for a vba code that when run, will open the email browser insert email addresses into the (To:) and have the workbook i'm runnin the vga code in attached to this email message. Is this possible? -- Et3rna ----------------------------------------------------------------------- Et3rnal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=445 View this thread: http://www.excelforum.com/showthread.php?threadid=26267 Hi see: http://www.rondebruin.nl/sendmail.htm -- Regards Frank Kabel Frankfurt, Germany "Et3rnal" <Et3rnal.1d0okp@excelfor...

Excel VBA
Hello All, I'm trying to compare two columns for new entries. Column A is the source information and column B is the coulmn to compare against. If there are new entries in column A I'd like the macro to record these new entries. Just the values would be perfect. For example if I have two columns with; ____A____B 1__105__105 2__106__106 3__107__107 4__108__108 5__109__109 6__111__111 7__112__112 8__113__113 9__114__114 10_115__115 11_116__116 12_117__117 13_118__118 14_119__119 15_120 16_121 I would like my macro to tell me that 120 and 121 are not in the second column. My column...