How to resolve 30/12/1899 as Date??

forum, I have included in my code to append records to a table as the code Loops.

In the Immediate Window, the records show correctly with correct dates.
But the Appended Records all show as 30/12/1899

I am pretty sure this date is incorrect as I wasn't even born then although sometimes my children would have thought so

Here is the Insert into sql which results in 30/121899

sqlString = "INSERT INTO TblLateFeeCalculated ( LDPK, LateFeeAmount, LateFeeDate ) " & vbCrLf & _
"VALUES (" & LoanID & ", " & LateFeeNow & ", " & CommenceDate & ");"
DoCmd.RunSQL sqlString

yet this Debug.Print results in the correct Non US date

Debug.Print LoanID & " " & LateFeeNow & " " & CommenceDate

Sponsored Links:

Related Topics

Date Range in Search Form as 12/30/1899?
Using Access 2010, I have a search form that includes both a start date and end date that must be entered each time a user searches. Both of these are unbound text boxes.

For the end date I want it to automatically use the most current date so I entered a default value of =Date().

The data behind the form started being collected on 5/1/2010 so I'd like to enter that as the default value for the Start Date field but it keep showing up as 12/30/1899. How can I fix that?


Very unusual data corruption in my table
I have a front-end client .MDB with forms/queries/reports/etc and linked tables. and a back-end .MDB with all the data tables. I have a table which used to have no corruption in the data. Field values entered were displaying and sorting properly.

The data in this table has become *partially* corrupted. One of the fields with corrupted data is a date/time field named "DateVerified".

A large majority of the values are absolutely fine - not corrupted at all. SOME of the records display the date 12/30/1899. but they appear in the"correct" sorted position!

After sorting the table by DateVerified in ascending order, one might expect all the records with a DateVerified of 12/30/1899 to appear first. MS Access is actually putting the records where they "belong", yet displaying the 12/30/1899 date. For example, a portion of the records after the sort:

07/25/05
07/25/05
12/30/1899
07/25/05
07/25/05

Access is displaying incorrect data - but in the correct sort position! It's like the data got messed up while leaving the index unaffected.

The same weirdness is happening with a few other fields in this table. Some fields are a long datatype, some are text. If I try sorting by the "SlotTechVerified" field, I see lengthy numbers like 1306525696, -285212672, etc.


Update query for 12/30/1899 dates
I am trying to run an update query to get rid of blank dates that are showing up as 12/30/1899, but when you click on the value in the table, it switches to 12:00:00 am.

Why would my update query miss some values?


Date Syntax not working
If the district = D1 and the Date Resolve is null then I want it to count the records. If I take out the date resolve part of the equation it works so there's something wrong in my syntax there.

=Sum(IIf([District]="D1",IIf([Date_Resolved]="",1,0)))


Displaying Time Only
I want to display a suggested time for when an event may have taken place. I thought I could use a date/time field with an input mask of 00:00 and it would capture only those four characters (hh:nn). I created a macro and the .txt file related to my query shows a date of 12/30/1899 followed by the inputted time of 12:15. How do I get rid of the inaccurate and undesired date and show just the


Cannot update date in table
I have a table having one date field formatted as short date "15.12.2011", and on a form text box formatted as short date format. When I click on text box calendar appears and I choose proper date, but when I want to update date in table via query it returns error.

My query looks like:
CurrentDb.Execute ("UPDATE category SET date=" & Forms![main form]!txtDate.Value), I don't see anything wrong but it keeps me returning error

And If I use US format update works but with date 31.12.1899


Unable to update date in table
I have a table having one date field formatted as short date "15.12.2011", and on a form text box formatted as short date format. When I click on text box calendar appears and I choose proper date, but when I want to update date in table via query it returns error.

My query looks like:
CurrentDb.Execute ("UPDATE category SET date=" & Forms![main form]!txtDate.Value)
I don't see anything wrong but it keeps me returning error

And If I use US format update works but with date 31.12.1899


VBA SQL syntax
I am having problems with an SQL statement in VBA.

My code is
.....

The above code does work (in a fashion) but creates a new invoice putting in the ID and a date of 31/12/1899 only.

I realise that we need a specific syntax for different variables and the variables above are
Bref = Integer
BAmount - Currency
Paid - Boolean
BDue = Date


Calculating Dates
I have two bounds fields on a form both fields are a medium date format both in the table design and the form view properties.

Field #1 = is a user entered date
I want Field #2 to be = Field #1 plus 18 months (i.e. 547 days)

In the form properties for Field #1 I have the AfterUpdate option set to run a macro.

The macro uses the SETVALUE option as follows:

Item [Field #2]
Expression =[Field #1]+547

Now in theory when Field #1 is updated, the macro should add 547 days to Field #1 and put that new date into Field #2. However, the only date it returns in Dec-30-1899 which I know is the zero date for how Access calculates dates.


Update SQL Date Issue
I am building a password table that when certain criteria are met, the user must change their password. Everything works which is nice, but this update statement goes bad for some reason. I put a code break on the line of code and everything shows the correct information for the variables, but when I proceed the date entered into the table is 12/30/1899 for some reason. Note when I highlight Date on the code break is shows as 7/9/2010. Any ideas?

db.Execute "UPDATE tbl_EmployeePasswords " _
& "SET Password = '" & strNewPassword & "'," _
& " CreationDate = " & Format(Date, "Short Date") & " " _
& "WHERE EmployeeID = '" & strUserID &amp


Unable to change the format of a date/time field in a table linked to Excel
I'm using Access 2010 with Windows 7. My table was fine as it was with a date field & a time field. The queries, forms, reports based on this table were all working beautifully.

I just thought it would nice if I could link it to an excel spreadsheet to spare me from entering data twice. It's linked but now there's a major problem.

With the date field there is no such issue. But with the time field, it displays the date with the time, & because only the time is entered in that field the date appears 30/12/1899. I understand why this has happened, but what I don't understand is why, when I try to change the format to Long Time in datasheet view, will it not let me? I mean it let's me change it so it looks how it should & I successfully save the change.

But when I run the query again, it returns as the way it was & when I re-open the table, I find that it has reverted back to how it was.


Text To Date Issue
So I have all dates fields set as text fields within Access, so its format is like y-mm-dd-hh-mm.000000 etc. I can use Left(date,10) to get Y-MM-DD, I can live with this rather than DD-MM-Y.

MY issue now is I need to be able to add a number (leadtime) to this date, but it doesn't like it.

How can I resolve this? I've tried all sorts of formatting the date via properties, using cdate, even left,mid,right etc to get format I like, but whatever I do, I still can not for thelife of me get a made up field within a query to day date (being text) + leadtime (being a number)


Lapse days
in my form I have three text boxes for year, month and days and I have two date field for start date and end date. from the entries of these two date field the difference has to be supplied to the text fields. normally subtracting the start date from the end date will give the result. but what I am facing is that when the difference is in days my idea does not work and the subtraction gives me some thing like the year 1999 and so. if I use DateDiff function then I get the days but converting the days into days, month and year again gives me a wrong difference in dates because of the actual months days. if any body have any idea to resolve this problem then


XML Importing date
I am importing an XML file into an Access 2007 database. In the file (below) some rows have a date e.g. <Document_date>01/01/2011</Document_date>.

However after importing, the date in the database is "30/12/1899". I am using the following code to import the file
"Application.ImportXML strPathFile, acAppendData".

The dateformat on the table is set "Short Date".

Does anyone know how to fix this so that the date on the file is inserted correctly into the table?
Finbarr

File:
<?xml version="1.0"?>
<tblDeliverytmp>
<Delivery_no>D0019</Delivery_no>
<Delivery_item>90001</Delivery_item>
<Document_date>01/01/2011</Document_date>
<Delivery_date>01012011</Delivery_date>
<Dispatch_Date>01/01/2011</Dispatch_Date>
<Customer_number>12345</Customer_number>
<PO_number>PO4590000343</PO_number>
<Quantity>123.3</Quantity>
<Quantity_unit>KG</Quantity_unit>
</tblDeliverytmp&gt


Input mask messed up my field data type
I have a date/time field called MyDate. Originally, its format is mm/dd/yyyy and there is no input mask. The Date Picker is also set. I then added input mask using Short date (99-99-0000;;_) and change the format to mm-dd-yyyy. However, when I switch to DataSheet view , all my dates are changed from mm/dd/yyyy to mm-dd-yyyy (which is the correct behaviour right? ) and when I tried to enter a new date in it says "The value you entered does not match Date/Time data type in this column". Plus, the DatePicker is missing. How can I resolve this


Error converting data type DBTYPE_DBDATE to date
I'm getting this error running an openquery "Error converting data type DBTYPE_DBDATE to date." Exist a workaround to resolve this?


Null Date value display in Microsoft Access 2007
Error in Empty Date field

In a database for Disciplinary Hearings, where the ValidityEndDate is calculated depending on the entry in WarningType and thus NoMonths and the entry in ValidityStartDate.

If there is a date entered in ValidityStartDate it works perfectly.

When there is no entry in the field, ValidityStartDate, this causes #Error to be displayed in the field ValidityEndDate. I dont want that #Error to be displayed. I understand that the problem exists because of multiplying by zero, but I need to know how to resolve it e.g. with Conditional Formatting if all else fails.

My criteria for the calculated query field (ValidityEndDate) are as follows:

CDate(DateAdd("m",([NoMonths]),([ValidityStartDate


Excel to Access 2010 Data Import Errors
I have tried many ways that I found on the web to solve this issue: when I import my Excel 2010 spreadsheet to Access 2010, the date field is coming back with type conversion failure on the date field.

The spreadsheet has 59,000 rows. How can I quickly resolve this problem? I have tried changing the Excel file to a csv file and it comes back with even more errors.


Data type mismatch in criteria expression
I have been trying to resolve an issue for 2 days now. I have tried to write a query which looks at the date each policy commences and based on that, I want to state what the next anniversary is. I have broken this down so that I have one column for the year (which is either this year or next year, depending on where the anniversary lands), then the month, then the day.

This all seems fine, but then as soon as I try to bring all 3 values together to make a date - I have tried CDate and DateSerial for this - then I am unable to filter this column. I just get the error message "Data type mismatch in criteria expression".

Anyone have any ideas what may be causing this issue


Confusing Date Format Issue
I've got an issue with date formatting that I cannot resolve.
I have the following codes:

Dim CurrentDate As String Dim rstDaily_Date As Object Dim rstFile_Date As Object Dim dbDatabase As Object
CurrentDate = Date CurrentDate = Format(CurrentDate, "MMDDYYYY") Set dbDatabase = CurrentDb Set rstFile_Date = dbDatabase.OpenRecordset("File Date") Set rstDaily_Date = dbDatabase.OpenRecordset("Date of Data")
'Change Date of Data for Daily files to proper value
rstDaily_Date.Edit
rstDaily_Date("Date of Data").Value = rstFile_Date("LastRunDate")
rstDaily_Date.Update
'Change date to Current Date
rstFile_Date.Edit
rstFile_Date("LastRunDate").Value = CurrentDate
rstFile_Date.Update

The tables/records its referring to are also formatted MMDDYYYY. however, when I attempt to run it, I get the error messagE:
"Run-time error '3421':
Data type conversion error.

Any thoughts on why this would be the case if everything appears to be in the same format