Problem with date field when updated by VBA (1 Viewer)

[mahenkj2]

I need some help relating to updating a date field in a table.

This field is updated by below code:

CurrentDb.Execute "INSERT INTO tblTransaction (TransactionPK, bottleFK, Loadingtime,rollingMachineFK,loadedby) values (" & TxPK & "," & Me.lstLoaded.Column(2) & ",#" & Now() & "#," & Me.optRollingMachine & ",'" & [CurrentUser] & "')"

The date field is updated in mm-dd-yyyy format whereas our system dates are in dd-mm-yyyy format.

In this forum and on google, I found that VBA's default date format is mm-dd-yyyy, OK, that's fine.

But, then how to immune application from these different date formats? I use datediff on a form to get the difference of time between date field of table and now(). That comes totally wrong because date field of table is wrong as per our system calender setting.

=IIf([Status]="loaded",DateDiff("n",DMax("loadingtime","tblTransaction","[bottlefk] = [Forms]![frmfilter].[BottleID]"),Now()),"NA")

Please advise.

 

[Isskint]

you can use the datevalue() function on this, however even that has its limitations. It does not work in SQL i think.

The 'answer' is to provide the user a date picker. That way they do not know what format is being used and format() (or FormatDatetime()) should display in the format you want, as well as datediff() working correctly.

 

[John Big Booty]

As an aside the Now() function includes a time component, so unless you specifically need it (the time component) use Date() instead.

 

[mahenkj2]

Thanks for your inputs.

As an aside the Now() function includes a time component, so unless you specifically need it (the time component) use Date() instead.

I am tracking time period of products on a machine for loading and unloading, so I think I need that.

you can use the datevalue() function on this, however even that has its limitations. It does not work in SQL i think.

I can check this.

The 'answer' is to provide the user a date picker. That way they do not know what format is being used and format() (or FormatDatetime()) should display in the format you want, as well as datediff() working correctly.

Every few minutes there is some transaction, and I am using bar coded products, so I want to keep free user from manual entry of date/time.

At the moment, I did this with some way around:

I was inserting loading time by VBA from a button on click event and inserting unloading time by an update query. So, loading time was appearing wrong and datediff was returning wrong values. Now, I removed loading time from SQL statement and used with another additional update query. So, both time fields are correct.

Thanks again.