Results 1 to 11 of 11
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Query from form (2002 SP-2)

    Trying to run a report from a query based on info provided by a form. All of the following parameters are not working and return an "unknown" value.

    Like "***" & [(Forms]![frmDateParametersSchedule]![txtCustAcctNo] & "***"
    Between ([Forms]![frmDateParametersSchedule]![oleDTPickerFrom]) And ([Forms]![frmDateParametersSchedule]![oleDTPickerTo])

    The first parameter is to return an account number (txt) without having to enter the entire number.
    The second parameter is to return the date range of the report (I am using this same parameter elsewhere without any problems). I've checked to see that the entry form, query, and report are all properly associated and don't see any errors there. Really baffled at this point.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query from form (2002 SP-2)

    Where does it not work? In the query, or only in the report? And what do you get? #Name, or #Error, or something else?

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Query from form (2002 SP-2)

    Sorry. I suppose that would have been helpful. It happens when I click on the cmdbtn to open the report.
    Attached Images Attached Images
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query from form (2002 SP-2)

    That looks very strange. Are the form parameters in the query, or do you pass them in the WhereCondition argument of DoCmd.OpenReport?

    If they are in the query, could you post the complete SQL of the query?
    If you pass them in the WhereCondition, could you post the complete WhereCondition?
    Thank you.

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Query from form (2002 SP-2)

    SELECT qryMasterAll.*, tblColours.Colour
    FROM tblColours INNER JOIN (qryMasterAll INNER JOIN tlkpOrderStatus ON qryMasterAll.txtOrderStatus = tlkpOrderStatus.Status) ON tblColours.Value = tlkpOrderStatus.ColorIndex
    WHERE (((qryMasterAll.txtCustomerNo) Like "***" & [(Forms]![frmDateParametersSchedule]![txtCustAcctNo] & "***") AND ((qryMasterAll.txtControlNo)<>"IsNull") AND ((qryMasterAll.dtmShConDate1) Between ([Forms]![frmDateParametersSchedule]![oleDTPickerFrom]) And ([Forms]![frmDateParametersSchedule]![oleDTPickerTo])));

    <font color=blue>Sorry about the attachment, I didn't realize it would come out that way</font color=blue>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Query from form (2002 SP-2)

    This is the complete process:

    frmLoadBoardTenDay has cmdMultiOrderReport. This opens frmDateParametersSchedule (unbound); the parameters are entered and cmdOpenReport opens rptDispatchMultiOrderSchedule, which is based on qlkpMultiOrderReport (the SQL statement)
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query from form (2002 SP-2)

    No need to apologize, it's just that I have never seen a parameter prompt in this form. If you have frmDateParametersSchedule open, and you try to open qlkpMultiOrderReport from the database window, do you get the same parameter prompt?

    By the way, there is no need to use multiple asterisks, one asterisk * already stands for an arbitrary number of characters.
    And can txtControlNo really have the value "IsNull" ? If you want to specify that txtControlNo must not be null (blank, empty), you must use
    ... ((qryMasterAll.txtControlNo) Is Not Null) ...

  8. #8
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Query from form (2002 SP-2)

    Yes, the same prompt appears.
    (You'd be so proud. I actually tried that before my original post. I'm learning. . .just really slow)
    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query from form (2002 SP-2)

    I'm the one who is slow tonight - the order of the rounded and square opening brackets is wrong in

    Like "***" & <big>[(</big>Forms]![frmDateParametersSchedule]![txtCustAcctNo] & "***")

    It should be

    Like "***" & <big>([</big>Forms]![frmDateParametersSchedule]![txtCustAcctNo] & "***")

  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Query from form (2002 SP-2)

    Thanks for the corrections in the SQL.
    I just re-created the qry using criteria in the qry and it works just fine.

    SELECT qryMasterAll.*, tblColours.Colour
    FROM tblColours INNER JOIN (qryMasterAll INNER JOIN tlkpOrderStatus ON qryMasterAll.txtOrderStatus = tlkpOrderStatus.Status) ON tblColours.Value = tlkpOrderStatus.ColorIndex
    WHERE (((qryMasterAll.txtCustomerNo) Like "*" & [Enter first three letters of Customer Acct#] & "*") AND ((qryMasterAll.txtControlNo)<>"IsNull") AND ((qryMasterAll.dtmShConDate1) Between ([Enter beginning load date]) And ([Enter ending load date])));

    I really don't get why it won't retreive the data from the Forms! prompt.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Query from form (2002 SP-2)

    <img src=/S/drop.gif border=0 alt=drop width=23 height=23>
    And now for the philosophical. . .
    Why do only really dumb mistakes happen in a public forum?

    Thanks, once again, for saving my bum.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

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