tbl Modified date

[matrixindicator]

Hallo, Hey,

I wonder, is it possible to get along vba the date out of the date + time stamp of the Modified property of a table.

tblTable 2007-05-22 20:12:02 PM

 

[orna]

Take a look at the system table SysObjects.
Type = 1
Name = YourTable

 

[matrixindicator]

I found it. I did it this way easy.

1. Unhide MSysObjects Table.
2. From this table get along a little query the DateUpdate field.
3. Get this info in your code along a recordset.

 

[ddevil]

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

 

[orna]

dim a
a = FormatDateTime(dtCompleteDate, vbShortDate)

 

[ddevil]

That worked, thank you!

 

[JoeAtWork]

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 have to wonder why go through the trouble? All dates have a time element, all you have done is change the original time to 00:00:00 (i.e. midnight).