Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    format Date in query (Access2000)

    Hi Guys,
    How do u format date in query? Is it format[feildname]dd/mm/yy? coz I tried that and it doesn't seem to work <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: format Date in query (Access2000)

    Hi,
    Either Format([fieldname],"dd/mm/yyyy")
    or right-click on the field in the query grid and set the format in the properties dialog.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format Date in query (Access2000)

    THANX Rory,
    It works fine.
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  4. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format Date in query (Access2000)

    Sorry,
    One more thing:
    If I want the criteria to be date,
    Default todays date and the user has a choice of entering a date, How do I do it? Is it Date(),[Enter Date]

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: format Date in query (Access2000)

    Try:
    IIf(nz([enter date],0),[enter date],Date())
    as a criterion.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format Date in query (Access2000)

    THANK YOU!
    That works fine, THANX!
    <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15>

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format Date in query (Access2000)

    I tried to use that for another query but it gives me error messages, why is this rory??
    The error message is:
    Run time error 3070:
    The microsoft jet database engine does not recognise '[enter date]' as a valid feild name or expression.

    <img src=/S/help.gif border=0 alt=help width=23 height=15> PLEASE <img src=/S/help.gif border=0 alt=help width=23 height=15>

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: format Date in query (Access2000)

    I'm not sure - can you post the SQL so I can check it looks OK?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format Date in query (Access2000)

    Im using sooooo many queries to do this coz Im linking alot of the tables to get data.
    It works for the query which am using to output to spreadsheet, but its not working for those which Im using to output to notepad & linking to a mail merged document so there must be something with the VB codes.
    The following is the SQL code of the query which all the queries are based on:
    SELECT Trade.tnum, Sec.ticker AS id, Sec.name, Sec.cusip, Sec.isin, Sec.sedol, Trade.clr2, Trade.tt, Trade.td, Trade.settles, Trade.tccy, Trade.q, Trade.tc, Trade.tp, Trade.tai, Trade.tax, Trade.moneyspot, Trade.notes, Trade.cancel, Trade.fund, Trade.splitref, TradeCpty.shortname, TradeCpty.cpty_name, Trade.ctc
    FROM Sec INNER JOIN (TradeCpty INNER JOIN Trade ON TradeCpty.cpty = Trade.cpty) ON Sec.id = Trade.id
    WHERE (((Trade.td)=IIf(nz([enter date],0),[enter date],Date())))
    ORDER BY Trade.tnum;

    The code that is giving me the error:
    <pre>Const CSTR_SAVEPATH As String = "S:SRI_WORK_AREADOCUME~1"
    Const CSTR_DOCSPATH As String = "C:TradarDevelopment"
    Sub ClassicFax()
    Dim strFileName As String, strMsg As String, vResult As Variant
    Dim strFilenamePart As String, appWord As Object
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstRecipients As DAO.Recordset
    Dim strFund As String
    Dim objWord As Object, strMessage As String, strMsgTitle As String
    strFund = "Classic"
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Classic")
    If rst.BOF And rst.EOF Then
    vResult = MsgBox("There are no records. Would you like to send a fax?", _
    vbQuestion + vbYesNo + vbMsgBoxSetForeground)
    If vResult = vbYes Then
    'set value to merge
    Set rstRecipients = db.OpenRecordset("tblRecipient", dbOpenDynaset)
    With rstRecipients
    .MoveFirst
    .FindFirst "[Fund] = '" & strFund & "'"
    .Edit
    !Merge = True
    .Update
    End With
    Set rstRecipients = Nothing
    'Output data to spreadsheet
    strFileName = CSTR_DOCSPATH & "FAXSOURCE.xls"
    DoCmd.OutputTo acOutputQuery, "qryRecipient", acFormatXLS, strFileName, False
    'Open fax cover
    Set objWord = GetObject(CSTR_DOCSPATH & "Fax.doc", "Word.Document")
    objWord.Application.Visible = True
    DoCmd.OpenQuery "UpdRecipients(Merge)", acNormal, acEdit
    End If
    Else
    strFileName = CSTR_DOCSPATH & "BCPSOURCE.xls"
    DoCmd.OutputTo acOutputQuery, "Classic", acFormatXLS, strFileName, False
    Set objWord = GetObject(CSTR_DOCSPATH & "Classi~2.doc", "Word.Document")
    'Make word visible.
    With objWord
    .Application.Visible = True
    'Execute the mail merge.
    With .MailMerge
    .destination = wdSendToNewDocument
    .Execute
    End With
    Set appWord = .Application
    End With
    appWord.DisplayAlerts = wdAlertsNone
    objWord.Close
    appWord.DisplayAlerts = wdAlertsAll

    strFileName = CSTR_SAVEPATH & "ClassicF" & Format(Now, "DDMMYY") & ".doc"
    strMsgTitle = "Buttonwood Trade Administration System"
    vResult = Dir(strFileName)
    If vResult <> "" Then
    strMessage = "File " & strFileName & " already exists," & _
    vbCrLf & vbCrLf & "Would you like to overwrite that file?" & _
    vbCrLf & vbCrLf & "Click Yes to overwrite the file" & _
    vbCrLf & vbCrLf & "Click No to save the file with another name" & _
    vbCrLf & vbCrLf & "Click Cancel to return to the document without saving."
    vResult = MsgBox(strMessage, vbQuestion + vbYesNoCancel + vbMsgBoxSetForeground, strMsgTitle)
    Select Case vResult
    Case Is = vbYes
    With appWord
    .DisplayAlerts = False
    .ActiveDocument.SaveAs filename:=strFileName, FileFormat:=wdFormatDocument
    MsgBox "Document has been saved.", vbInformation + vbMsgBoxSetForeground, strMsgTitle
    .DisplayAlerts = True
    'objWord.Activate
    End With
    Case Is = vbNo
    strFileName = InputBox("File " & strFileName & " already exists," _
    & Chr(10) & "Please enter another filename not including " _
    & Chr(34) & ".doc" & Chr(34) & ": ") & ".doc"
    If strFileName = ".doc" Then
    MsgBox "You've clicked on cancel," _
    & " the document has not been saved.", vbInformation + vbMsgBoxSetForeground, strMsgTitle
    Else
    appWord.ActiveDocument.SaveAs _
    filename:=CSTR_SAVEPATH & strFileName, FileFormat:=wdFormatDocument
    MsgBox "Document has been saved.", vbInformation + vbMsgBoxSetForeground, strMsgTitle
    'objWord.Activate
    End If
    Case Else
    MsgBox "You've chosen not to save the document.", vbInformation + vbMsgBoxSetForeground, strMsgTitle
    End Select
    Else
    With appWord
    .DisplayAlerts = False
    .ActiveDocument.SaveAs filename:=strFileName, _
    FileFormat:=wdFormatDocument
    .DisplayAlerts = True
    MsgBox "Document has been saved.", vbInformation + vbMsgBoxSetForeground, strMsgTitle
    'objWord.Activate
    End With
    End If
    AppActivate "Microsoft Word" ' Activate Microsoft Word.
    End If
    cleanup:
    On Error Resume Next
    Set objWord = Nothing
    Set appWord = Nothing
    Set rst = Nothing
    Set db = Nothing
    End Sub
    </pre>


    The error is "Too few parameters. Expected 1.
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/help.gif border=0 alt=help width=23 height=15>

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: format Date in query (Access2000)

    Have you explicitly declared [enter date] as a date parameter within your query, or did you simply add it to the criteria field? If the latter, try declaring it as a date parameter and see if that helps. (Right-click on a blank part of the gray bit of the query design window and choose Parameters.)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format Date in query (Access2000)

    Hi Rory!
    Thanx, I

  12. #12
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format Date in query (Access2000)

    Thanx Rory!
    It works, all I had to do was create an append query and append that records to a query and get it into another query before outputting to create reports.
    For some reason, it didn't like me getting the records straight from the parameter query into the other query.
    What matters is that it works now.
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Thanx.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •