Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Location
    Texas
    Posts
    37

    Unanswered: How do I convert string to date

    Dim myDate As Date
    Dim myTime As Variant
    Dim myString As Variant

    'I've got a form with a calendar function where users select a date
    myDate = Me.ActiveXCtl0.Value 'example 02/15/2004

    'I've got a combo box for users to pic certain times
    myTime = Combo7 'example = 00:00:00

    'how do I concatenate myDate and myTime together?
    'I seem to only be able to do this as a string.
    'how do I convert the concatenated string to a Date format?
    myString = myDate & " " & myTime


    DoCmd.OpenReport "report", acViewPreview, , "[start_date] >= mystring"
    'I want to use the mystring variable as a qualified when I launch the
    report form. The above DoCmd doesn't work.

    Any help is truly appreciated.
    A2K - W2k
    Thanks & Regards
    Pete

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    very easy!
    DIM datformat as string
    datformat="dd-mmm-yy" 'or any other format , you need
    myString = Format(myDate, datformat) & " " & myTime
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: How do I convert string to date

    Originally posted by pltx2000
    Dim myDate As Date
    Dim myTime As Variant
    Dim myString As Variant

    'I've got a form with a calendar function where users select a date
    myDate = Me.ActiveXCtl0.Value 'example 02/15/2004

    'I've got a combo box for users to pic certain times
    myTime = Combo7 'example = 00:00:00

    'how do I concatenate myDate and myTime together?
    'I seem to only be able to do this as a string.
    'how do I convert the concatenated string to a Date format?
    myString = myDate & " " & myTime


    DoCmd.OpenReport "report", acViewPreview, , "[start_date] >= mystring"
    'I want to use the mystring variable as a qualified when I launch the
    report form. The above DoCmd doesn't work.

    Any help is truly appreciated.
    And as for your conversion: CDate(MyDateString) ...

  4. #4
    Join Date
    Feb 2004
    Posts
    199
    CDate(MyDateString) won't have afect, that means
    myDate & " " & myTime
    and
    CDate(MyDateString) & " " & myTime
    will have same text result since VB makes conversion by default, BUT, since pltx2000 uses converted data in a criteria, he may need formated conversion to be shure that SQL parses text to Data corectly (in case of CLient-Server app, client adn Server may have different language settings)
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  5. #5
    Join Date
    Jan 2004
    Location
    Texas
    Posts
    37

    passing a variable in docmd.openreport statement

    The CDate(mystring) conversions works fine. Thanks!

    Dim myDate As Date
    Dim mystring As String

    mystring = "02/15/2004 07:00:00"

    myDate = CDate(mystring)

    ' I want to use the myDate variable as an argument in docmd.openform but I can't get this to work.

    I've tried (below) none works ... suggestions?

    DoCmd.OpenReport "report", acViewPreview, , "[start_date] >= #myDate#" <- gives syntax error

    DoCmd.OpenReport "report", acViewPreview, , "[start_date] >= myDate" <- an "enter parameter value" input box appears

    DoCmd.OpenReport "report", acViewPreview, , "[start_date] >= [myDate]" <- an "enter parameter value" input box appears
    A2K - W2k
    Thanks & Regards
    Pete

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    Just a guess:

    DoCmd.OpenReport "report", acViewPreview, , "[start_date] >= '" & myDate & "'"

    otherwise...take a look in your table and see how the data is displayed when the table is Opened. You may need to use the Format function within your Where clause to match what is displayed in the table's start_date field.


  7. #7
    Join Date
    Jan 2004
    Location
    Texas
    Posts
    37

    vbGeneralDate format

    my field is in vbGeneralDate format ... what is the syntax to use in the where section of the docmd.openform? I've looked for samples but can't find any.
    A2K - W2k
    Thanks & Regards
    Pete

  8. #8
    Join Date
    Jan 2004
    Location
    Texas
    Posts
    37

    continued

    my bad ... meant docmd.openreport
    A2K - W2k
    Thanks & Regards
    Pete

Posting Permissions

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