create a query based on two list box values?

[chris davies]

Hi Folks,
Could someone please point me on the right direction, I need to create a query that is based on the selection of two list boxes, where the selections are dates.

I have a basic table which contains a date and the hours worked for that date. I need to select a start date and an end date and display all the hours worked between and including the selected dates. I have my list boxes populating fine, but I am at a loss at how to create the query and display the result. Any help here would be greatly appreciated.

 

[pbaldy]

A criteria of this should work:

Between Forms!FormName.ListBoxOne And Forms!FormName.ListBoxTwo

 

[chris davies]

sorry I didn't reply to your post earlier, I was locked out from replying for some strange reason, I believe it was a permissions error. Anyways, I made an error previously when I said two list boxes, I should have stated two combo boxes.

I tried what you suggested but Access comes back with an "expression too complex" error message.

I was supposed to enter your suggestion as the criteria of the query yes?

This shouldn't be difficult from where I am looking at the problem, I know I haven't used Access for quite a while ( been busy completing a degree using other technologies, successfully may I add, BSc Hons with a 2:1 and shamefully can't get an Access query to run) but sheesh, this is beginning to boil my swede. All I want to do is have two combo boxes, which are already populated with dates, and have a command button to click to retrieve the hours worked between ( and including) the selected dates. Am I missing something here, or is it too difficult?

Any further help from anyone would be very gratefully received. Thank you in advance for any or all help offered.

 

[pbaldy]

That would be a criteria, yes. One thought is to check the bound column of the combos if they have more than one column in them. I use date criteria like that all the time, though I always use textboxes so the user can type in whatever dates they want. It might help to post the SQL of the query if you're still stuck, or even better a sample db.

 

[chris davies]

not sure what you mean by "bound" I created a query using the wizard that shows all the dates and hours worked on that particular date and I used this query for a basis of a form, which utilises the query to populate the combo boxes. As for what to do next, to be honest I am stuck.

Could you please clarity what you mean by bound. I have just looked at my form in design mode and I notice that both of the combo boxes are "unbound".

 

[chris davies]

PS how do I add a sample Db to a post?

 

[chris davies]

I don't suppose you could give me a step my step instruction on how to achieve this could you?

 

[chris davies]

SO far I have got to using this:

SELECT Sum(Hours.Hours) AS TotalHours
FROM Hours
WHERE (((Hours.Date)>=[Forms]![hoursCheck].[cmbStartDate] And (Hours.Date)<=[Forms]![hoursCheck].[cmbEndDate]));

as the criteria of rhe query, but when I run the query from the command button on my form, the user keep getting a message box prompt to enter the dates, when what I need is for the dates from the combo boxes to be used. My head is hurting.

 

[pbaldy]

It will be easiest with sample db. If you compact/repair then zip the db, it should be small enough to attach here (in the advanced reply area).

 

[chris davies]

here is the sample db, hope it is small enough, you will notice that I have created a number of queries based on the hours table, the date being the primary key. I heard a few people complaining about using a date as a primary key ut since it is totally unique, personally I don't see any problem. Also the dates are being saved in UK format. The two queries I have been working on are prompt hours and combo box.

I hope you can make some sense out of this, thanks anyway for taking the time to look.

 

[chris davies]

OK, I now have something working using the following code:



Private Sub cmdDisplayHours_Click()
On Error GoTo Err_cmdDisplayHours_Click

Dim stDocName As String


If IsNull(Me.cmbStartDate) Then
MsgBox "Please Enter A Start Date!"

ElseIf IsNull(Me.cmbEndDate) Then
MsgBox "Please Enter An End Date!"

ElseIf Me.cmbStartDate >= Me.cmbEndDate Then
MsgBox "End Date must be HIGHER than Start Date"
Else

stDocName = "PromptHoursQuery"
DoCmd.OpenQuery stDocName, acNormal
DoCmd.Close stDocName
End If

Exit_cmdDisplayHours_Click:
Exit Sub

Err_cmdDisplayHours_Click:
MsgBox "me.cmbStartDate = " & Me.cmbStartDate.Value

MsgBox "me.cmbEndDate = " & Me.cmbEndDate.Value

MsgBox "query Failed"
Resume Exit_cmdDisplayHours_Click

End Sub

But can I display the results of my query on the same form instead of the query opening in a separate window?

 

[pbaldy]

I guess I need a clarification on the problem. If I choose dates in those combo boxes the query opens without prompts.

To answer your most recent question, if you just want to display the result you don't even have to open the query:

Me.txtResult = DLookup("totalhours", "[combo box hours query]")

 

[chris davies]

When I used the form, two consecutive prompts appear, asking the user to enter the dates required, I then changed the code sllighty, as per my last post, which now seems to work.


I then tried what you just suggested:

Me.txtResult = DLookup("totalhours", "[combo box hours query]")

Which I added as part of the onclick event for the command button.

and Access informs me that the query failed, obviously where my error routine kicks in. As I have never used DLookup before, I'm not sure if I have typed the code incorrectly or not, as I did a complete cut and paste job.

Actully now after playing with this, the value of me.txtResult appears as the string totalhours. My head is beginning to hurt again.

 

[chris davies]

nailed that one, the square brackets were around the wrong argument. Thanks for your help and patience

 

[pbaldy]

Glad you got it sorted. Sorry I didn't reply earlier; I was out playing golf.