Topics Search

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

How to resolve 30/12/1899 as Date??
Views: 155
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:

More topics

 

Date Range in Search Form as 12/30/1899?

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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