Record Count?

[slenish]

Hello,

I am trying to make a VBA function that will count the records in my table on a button click.

The hard part is I want the function to single out dates that appear more than (example 6 times) then diplay that date in a small pop up form in a list box or text box.

So for example if I run the date range 7/1/2010 - 7/31/2010 in a pop up box it will show all the dates in that range that appear in the table more than 6 times. I have been trying to use the DCount function but it either just diplays a number of how many there are or it just shows all the dates.

Any help would be great,
Thanks :)

[Delerna]

On your form put two textboxes to enter the from and to dates

create a button on the form that runs the following query when you click the button.


[code=sql]
SELECT DateField,count (DateField) as NumRecs
FROM theTable
WHERE DateField>=Form s!YourFormName. TheFromDateText boxName
AND DateField<=Form s!YourFormName. TheToDateTextbo xName
GROUP BY DateField
HAVING count(DateField )>=6

[/code]

You need to change
...theTable
...DateField
...YourFormName
...TheFromDateT extboxName
...TheToDateTex tboxName
to suit

This is untested so you may need to adjust it a bit


When the query is working you can bind it to a listbox on your form
You will need to put
ListboxName.req uery
into the click event of the button

[slenish]

Hi Delerna,

Thanks so much for the help. If you were just typing that out off the top of your head with out testing it, you are the man! Works great!!

I just need to adjust one thing. How can I hide the normal query window? I get the date to pull in to the list box with no problem but at the same time the normal query window pops up in the background. How can i make that not happen??

Thanks so much for the help!!

[NeoPa]

That doesn't appear to be related to this code Slenish. I expect you have some other code (that we have no information about) that is doing this. You need to find and eliminate this code.

[Delerna]

Agree with Neopa .... however I have a sneaking suspicion that you created the button to run the query as I suggested and now that you have bound it to the listbox you didn't remove the code that runs the query from the buttons click event.

[slenish]

Hello NeoPa and Delerna,

You are both right! Delerna your suggestion is what the problem was. I had on the button click that it would run the query so I removed that part of the code and just left the button to requery the list box and its working great now.

Thanks again for the help :)