Query and a listbox 2

[zyphac]

Hello all. I have a listbox and a query that lists the last 18 days. What I need it to do is type in a date range for the query then populate my listbox with that data. I have tried several ways, but no luck. Could any one enlighten me on this subject. Many thanks!
zypha

 

[Trendsetter]

On the form that holds your listbox enter 2 textboxes set up as dates - strDateStart and strDateEnd.

In the query that populates the listbox under the Date you are searching against enter:

BETWEEN [Forms]![frmwithlistboxname]![strDateStart] AND
[Forms]![frmwithlistboxname]![strDateEnd]

You could attach a calendar to the two textboxes which would be really smooth

 

[zyphac]

I put the two text boxes up, and the sql string, it did not do any thing here is the listbox string:

SELECT [Contract data Query].Date, [Contract data Query].ConfirmationID, [Contract data Query].BuyerName, [Contract data Query].[Time of Shipment] FROM [Contract data Query] WHERE ((([Contract data Query].Date) Between Forms!billing!strDateStart And Forms!billing!strDateEnd));
Am I doing something wrong? Thank you for your post!!
zypha

 

[Trendsetter]

When you "open" this query in the Query Builder are you asked for the two dates??
If so when you complete them (with this format type 26/03/2003) and enter are any records shown in the dataform???

If so then it may a requery on the listbox to present the records that have been selected. you can do this manually by placing the cursor in the listbox and pressing F9.

Let me know what happens

 

[zyphac]

when I open the query builder it just opens, no asking for dates. when I click on the ... and put in BETWEEN [Forms]![billing]![strDateStart] AND [Forms]![billing]![strDateEnd]
under date, the list box lists nothing. if i take it out the query works, but not choosing my date range, hummmm, any more input, I 'll take it !!!!thanks
zypha

 

[zyphac]

Ok I got it to work, but is there a way I can use a button to update the list box instead of f9? Thanks Zypha

 

[Trendsetter]

Yes!
Put a command button on your form say "Activate"
In the code behind the button add:

CurrentDb.Execute "[Contract Data Query]"
Me.ListBoxName.Requery

The queryname would be better without any spaces and then would not need the square brackets

Access is the only database that allows spaces in names and it is VERY BAD PRACTICE try to get out of the habit!

 

[zyphac]

Thank you very much, I am chaging the name, shame on me smack, smack, Also I tried the code behind the button and it gives me error #3078 jet database can not find query.
If I take the [] away it says : it can not execute a select query. any Ideas?
Thanks again for your time and effort
zypha

 

[Trendsetter]

My fault:
CurrentDb.Execute only works with action queries ie: update, delete etc..
Smack, Smack
it should be:
DoCmd.OpenQuery "ContractDataQuery"

 

[zyphac]

Ahh that works, is it supposed to open the query too? It updates the list box, but the query opens too. Well your are the hero of the day, many thanks for you help and time zypha

 

[Trendsetter]

It's much easier when the application is in front of you than trying to work at a distance anyway...

In place of:
DoCmd.OpenQuery "ContractDataQuery"
with
Me![frmwithlistboxname].RowSource = "ContractDataQuery"
Me![frmwithlistboxname].Requery

This 'should' work...

 

[zyphac]

Hey thanks!!! uggg I hate moving. Don't move if you don't have too!! Thanks again for your help