Update Statement inserts Date incorrectly

G

Guest

Code: 
On Error GoTo Err_ErrHandler
Dim strCartName As String
Dim updateQry As String
Dim NumToOrder As Integer
Dim varDate As Date
Dim tmpDate As String
Dim strOnOrder As String
Dim x As Byte

varDate = Me.txtDate.Value
tmpDate = FormatDateTime(varDate, vbShortDate)

If Me.txtNumToOrder.Value > 0 Then
NumToOrder = Me.txtNumToOrder.Value
Else
x = Warning(strTitNull, "You Have Not Entered the Number of " & _
Me.cboCartName.Value & " to order!")
Exit Sub
End If

If Me.txtPPU.Value = 0 Then
x = Warning(strTitNull, "You have left the Price Per Unit Field
Blank!!")
Exit Sub
End If

'prevents the user leaving the cartridge field blank
If IsNull(Me.cboCartName.Value) = False Then
strCartName = Me.cboCartName.Value
Else
x = Warning(strTitNull, "You have left the Cartridge Field Blank!!")
Exit Sub
End If


'--------------------------------------------------------------
'Creates a current order
updateQry = "UPDATE tblOrderCurr SET " & _
"OnOrder=OnOrder+" & NumToOrder & _
", PricePerUnit=" & Me.txtPPU.Value & _
", OrderDate=#" & varDate & _
"# WHERE " & _
"((tblOrderCurr.CartridgeName)=" & Chr$(39) & strCartName & Chr$(39)
& ");"
DoCmd.RunSQL (updateQry)

updateQry = "UPDATE tblOrderCurr SET " & _
"TotalCost=(OnOrder*PricePerUnit) " & _
" WHERE " & _
"((tblOrderCurr.CartridgeName)=" & Chr$(39) & strCartName & Chr$(39)
& ");"
DoCmd.RunSQL (updateQry)

'Sets the chk box onorder in tblCartridges
strOnOrder = "UPDATE tblCartridges SET " & _
"OnOrder=-1 " & _
"WHERE " & _
"((tblCartridges.CartridgeName)=" & Chr$(39) & strCartName &
Chr$(39) & ");"
DoCmd.RunSQL (strOnOrder)

Me.Refresh

Exit_ErrHandler:
Exit Sub

Err_ErrHandler:
MsgBox "Error: " & Err.Number & ". " & Err.Description
Resume Exit_ErrHandler
End Sub

Here is an example of the string updateqry (its first instance)
"UPDATE tblOrderCurr SET OnOrder=OnOrder+30, PricePerUnit=400,
OrderDate=#08/12/2005# WHERE ((tblOrderCurr.CartridgeName)='Minolta
1710471-001');"

But when i look in my database table the above is dispolayed as:

CartridgeName
Minolta 1710471-001

OnOrder
30

PricePerUnit
£400.00

TotalCost
£12000.00

OrderDate
12/08/2005

Late
0

------------------------

Why is it reversing my date to put the month first.
My computers regional settings are UK and so is my timezone
And as you can see above when the date is in the insert statment it is in
the correct format so why is it being changed as soon as it is added to the
database record?
The field is set to short date and is displayed as the correct format in the
format selection box...
 
V

Van T. Dinh

Literal date values in the SQL MUST be in US-format "mm/yy/yyy" or an
unambigous format liek "yyyy-mm-dd" regardless of your Regional Settings.

Try:

....
", OrderDate=#" & Format(varDate, "mm/dd/yyyy") & _
....

--
HTH
Van T. Dinh
MVP (Access)



Ryan said:
Code: 
On Error GoTo Err_ErrHandler
Dim strCartName As String
Dim updateQry As String
Dim NumToOrder As Integer
Dim varDate As Date
Dim tmpDate As String
Dim strOnOrder As String
Dim x As Byte

varDate = Me.txtDate.Value
tmpDate = FormatDateTime(varDate, vbShortDate)

If Me.txtNumToOrder.Value > 0 Then
NumToOrder = Me.txtNumToOrder.Value
Else
x = Warning(strTitNull, "You Have Not Entered the Number of " & _
Me.cboCartName.Value & " to order!")
Exit Sub
End If

If Me.txtPPU.Value = 0 Then
x = Warning(strTitNull, "You have left the Price Per Unit Field
Blank!!")
Exit Sub
End If

'prevents the user leaving the cartridge field blank
If IsNull(Me.cboCartName.Value) = False Then
strCartName = Me.cboCartName.Value
Else
x = Warning(strTitNull, "You have left the Cartridge Field
Blank!!")
Exit Sub
End If


'--------------------------------------------------------------
'Creates a current order
updateQry = "UPDATE tblOrderCurr SET " & _
"OnOrder=OnOrder+" & NumToOrder & _
", PricePerUnit=" & Me.txtPPU.Value & _
", OrderDate=#" & varDate & _
"# WHERE " & _
"((tblOrderCurr.CartridgeName)=" & Chr$(39) & strCartName &
Chr$(39)
& ");"
DoCmd.RunSQL (updateQry)

updateQry = "UPDATE tblOrderCurr SET " & _
"TotalCost=(OnOrder*PricePerUnit) " & _
" WHERE " & _
"((tblOrderCurr.CartridgeName)=" & Chr$(39) & strCartName &
Chr$(39)
& ");"
DoCmd.RunSQL (updateQry)

'Sets the chk box onorder in tblCartridges
strOnOrder = "UPDATE tblCartridges SET " & _
"OnOrder=-1 " & _
"WHERE " & _
"((tblCartridges.CartridgeName)=" & Chr$(39) & strCartName &
Chr$(39) & ");"
DoCmd.RunSQL (strOnOrder)

Me.Refresh

Exit_ErrHandler:
Exit Sub

Err_ErrHandler:
MsgBox "Error: " & Err.Number & ". " & Err.Description
Resume Exit_ErrHandler
End Sub

Here is an example of the string updateqry (its first instance)
"UPDATE tblOrderCurr SET OnOrder=OnOrder+30, PricePerUnit=400,
OrderDate=#08/12/2005# WHERE ((tblOrderCurr.CartridgeName)='Minolta
1710471-001');"

But when i look in my database table the above is dispolayed as:

CartridgeName
Minolta 1710471-001

OnOrder
30

PricePerUnit
£400.00

TotalCost
£12000.00

OrderDate
12/08/2005

Late
0

------------------------

Why is it reversing my date to put the month first.
My computers regional settings are UK and so is my timezone
And as you can see above when the date is in the insert statment it is in
the correct format so why is it being changed as soon as it is added to
the
database record?
The field is set to short date and is displayed as the correct format in
the
format selection box...
Click to expand...
 
G

Guest

thanks..

but why then does the following happen ( I think this next bit should maybe
go into the coding section but its is somethig to do with the query..

Code: 
Private Sub Form_Load()
' Populate the cbo with order dates where On Order is greater than 0
Me.cboOrderDate.RowSource = "SELECT OrderDate " & _
"FROM tblOrderCurr " & _
"WHERE OnOrder>0 AND OrderDate>#01/01/1900# " & _
"GROUP BY OrderDate " & _
"ORDER BY OrderDate;"

' Me.cboOrderDate.Value = Me.cboOrderDate.ItemData(0)

If Me.cboOrderDate.Value <> "" Then
Me.frmsubOrderCurr.Visible = True
Call cboOrderDate_AfterUpdate
' Else
'     Me.frmsubOrderCurr.Form.RecordSource = ""
'     Me.frmsubOrderCurr.Visible = False
End If

End Sub

----------------------

Private Sub cboOrderDate_AfterUpdate()
On Error GoTo ErrorHandler

Me.frmsubOrderCurr.Form.RecordSource = "SELECT CartridgeName, " & _
"OnOrder, PricePerUnit, TotalCost FROM tblOrderCurr " & _
"WHERE OrderDate = " & "#" & Trim(Me.cboOrderDate.Value) & "#"

Me.frmsubOrderCurr.Form.Refresh

ErrorHandler_Exit:
Exit Sub

ErrorHandler:
MsgBox ("Error #: " & Err.Number & "; Description: " & Err.Description)
Resume ErrorHandler_Exit
End Sub

----------------------------


why does my combo box show the date that is in the field (as it should) but
if in the field teh date is 12/08/2005 (this is how it will display in the
cbo box) why do no records show but if i enter 08/12/2005 in the cbo box it
will... this also works vice versa...
 
V

Van T. Dinh

Why would you use Trim() on a date value? I used Format() in my previous
reply!

Trim is a String function accepts a String expression and returns a String
(actually a Variant of String subtype) so I am not sure why you used it on a
date value.

Internally, the date is NOT stored as 08/12/2005 or 12/08/2005 (Did you mean
12/Aug or 08/Dec?). It is stored similar to a Double where the integral
part (the date component) is the number of days since 30/Dec/1899 and the
fractional part represents time since midnight as a fraction of 1 day.

I wrote before that JET needs the literal date value in the format
"mm/dd/yyyy" REGARDLESS of your Regional Settings. Your ComboBox Value is
the internal value (i.e. the number of days since 30/Dec/1899) and it can be
formatted whichever way you choose. If you don't specify a Format, Access
will use the date format in the Regional Settings on your PC. Thus it is
possible for the ComboBox to display as 08/12/2005 (meaning 08/Dec/2005) but
in the SQL construction, you need to use #12/08/2005# to refer to the SAME
date value!

That why I advised you to use the Format() function to convert to the right
format for the SQL String construction. I would get rid of the Trim()
function in the SQL construction also.

--
HTH
Van T. Dinh
MVP (Access)



Ryan said:
thanks..

but why then does the following happen ( I think this next bit should
maybe
go into the coding section but its is somethig to do with the query..

Code: 
Private Sub Form_Load()
' Populate the cbo with order dates where On Order is greater than 0
Me.cboOrderDate.RowSource = "SELECT OrderDate " & _
"FROM tblOrderCurr " & _
"WHERE OnOrder>0 AND OrderDate>#01/01/1900# " & _
"GROUP BY OrderDate " & _
"ORDER BY OrderDate;"

' Me.cboOrderDate.Value = Me.cboOrderDate.ItemData(0)

If Me.cboOrderDate.Value <> "" Then
Me.frmsubOrderCurr.Visible = True
Call cboOrderDate_AfterUpdate
' Else
'     Me.frmsubOrderCurr.Form.RecordSource = ""
'     Me.frmsubOrderCurr.Visible = False
End If

End Sub

----------------------

Private Sub cboOrderDate_AfterUpdate()
On Error GoTo ErrorHandler

Me.frmsubOrderCurr.Form.RecordSource = "SELECT CartridgeName, " & _
"OnOrder, PricePerUnit, TotalCost FROM tblOrderCurr " & _
"WHERE OrderDate = " & "#" & Trim(Me.cboOrderDate.Value) & "#"

Me.frmsubOrderCurr.Form.Refresh

ErrorHandler_Exit:
Exit Sub

ErrorHandler:
MsgBox ("Error #: " & Err.Number & "; Description: " & Err.Description)
Resume ErrorHandler_Exit
End Sub

----------------------------


why does my combo box show the date that is in the field (as it should)
but
if in the field teh date is 12/08/2005 (this is how it will display in the
cbo box) why do no records show but if i enter 08/12/2005 in the cbo box
it
will... this also works vice versa...[/QUOTE]
Click to expand...
 
G

Guest

I did as you suggested but that was on frmAddOrder this is a different form
(frmReceiveOrder) used to receive orders.. The trim is because the combobox
that the date is in could be edited by a user and they could insert a space
to either side which may cause problems with the lookout it is just a
precaution.

this form parses the table tblOrderCurr to get any dates that are above
01/01/1900 (it wont leet me have a blank date field) it then puts these
dates into the itemdata array of a cbo box so the user can select the order
placed on that date. The cbo box afterupate procedure then sets the rowsource
of a sub form to display all items order on that date.
 
Top