I think what I need to do is similiar to what you are doing, but I'm not sure I understand. I have a date field in a table that has the time stamp along with the date and I want to get rid of the time stamp and just keep the date in short date format. I was trying to do this in VBA before I put the date into the table (the date comes from another source which has the time stamp with it). I've posted my code below. The error I keep getting is: Statement invalid outside type block. I don't understand what this means. (Code in question is offset by ********* below). Thanks for your help.
Sub GetRefundData(CriteriaWR)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim intEnterWR As Variant
intEnterWR = [CriteriaWR]
strSQL = "SELECT WRInquiry.* FROM WRInquiry Where CD_WR = " & intEnterWR & ";"
Dim stProjectID, stPremise, stArea, stCustName, stJobAddress, stBillName, stBillAdd, stBillCity, stBillState, stBillZip, stOpUnit, stUtilityType, stStatus, stWRDesc, stTypeWR, stJobType As String
Dim dtCompleteDate As Variant (bring in as variant so code doesn't fail if it's null)
Dim intWRNumber As Variant
Dim intTotal As Currency
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
With rst
intWRNumber = rst![CD_WR]
stPremise = rst![ID_PREMISE]
stArea = rst![CD_AREA]
stCustName = rst![NM_CONTACT]
If stCustName Like "*'*" Then
stCustName = Replace(stCustName, "'", "")
End If
stJobAddress = rst![JobAddress]
If stJobAddress Like "*'*" Then
stJobAddress = Replace(stJobAddress, "'", "")
End If
stBillName = rst![NM_CONTACT]
If stBillName Like "*'*" Then
stBillName = Replace(stBillName, "'", "")
End If
stBillAdd = rst![Address]
If stBillAdd Like "*'*" Then
stBillAdd = Replace(stBillAdd, "'", "")
End If
stBillCity = rst![AD_TOWN]
stBillState = rst![CD_STATE]
stBillZip = rst![AD_POSTAL]
stOpUnit = rst![CD_CREWHQ_FIN]
stUType = rst![IND_UTIL]
stStatus = rst![CD_STATUS]
stWRDesc = rst![DS_WR]
If stWRDesc Like "*'*" Then
stWRDesc = Replace(stWRDesc, "'", "")
End If
stTypeWR = rst![TP_WR]
stJobType = rst![TP_JOB]
dtCompleteDate = rst![DT_COMPLETE]
intTotal = rst![QuoteAmount]
stProjectID = rst![CD_WO_INSTL]
*********************
FormatDateTime(dtCompleteDate, vbShortDate) As Variant
*********************
strSQL = "INSERT INTO TempRefund_Customers ([ParentWRNumber], [CSSPremiseNumber], [Area], [CustomerName],[JobAddress], " & _
"[BillingAddressName], [BillingAddress],[BillingCity], [BillingState],[BillingZipCode], [OpUnit],[UType],[ProjectID],[RefundableTotal],[CompletionDate]," & _
"[Status],[WRDesc],[TypeWR],[JobType]) Values ('" & intWRNumber & "','" & stPremise & "','" & stArea & "','" & stCustName & "'," & _
"'" & stJobAddress & "','" & stBillName & "','" & stBillAdd & "','" & stBillCity & "','" & stBillState & "', " & _
"'" & stBillZip & "','" & stOpUnit & "','" & stUType & "','" & stProjectID & "','" & intTotal & "','" & dtCompleteDate & "'," & _
"'" & stStatus & "','" & stWRDesc & "','" & stTypeWR & "','" & stJobType & "')"
CurrentDb.Execute strSQL
End With
DoCmd.OpenForm "RefundableForm", , , "ParentWRNumber = " & intEnterWR
DoCmd.Close acForm, "Updating Data"
DoCmd.Close acForm, "CriteriaForm"
Set fso = Nothing
Set fd = Nothing
Set ts = Nothing
End Sub
