Query help

[rem2088]

I have 2 queries that I want to run each time a form is updated. The first one is a select query and the second one is a count query that adds all the records selected in the first. My problem is I don't want to see either of the queries, just the value returned from the second one. I can't figure out how to do this as the query keeps opening up in datasheet view after the record is updated.


Thanks as always.

-Ben

 

[pbaldy]

One way would be to have a textbox with a DLookup() function as its control source, referring to the second query (or a DCount() based on the first, and no need for the second).

 

[rem2088]

I'm not seeing how the DLookup would help me in this situation. It would appear that it can lookup a value in a table or some such.

-Ben

 

[pbaldy]

A table or query. If the name of your field in the second query was TheCount, this would retrieve it (presuming there's only one record returned):

=DLookup("TheCount","SecondQueryName")

But an even better solution is to eliminate the second query and count the records in the first query:

=DCount("*","FirstQueryName")

 

[rem2088]

What if I try this in the control source of the textbox :

=DCount("[PackageCount]", "tblPackageTracking", "Forms!FrmPackageTrackingUPS![Shipper]" AND" Forms!FrmPackageTrackingUPS![BatchNumber]" AND "Forms!FrmPackageTrackingUPS![Date] >=DateAdd("d",0,Date())

What I am hoping this will give me is " Please give me a count of All the records that are sorted down by the Shipper in the shipper box in the form, the BatchNumber in the batchnumber box and the date's that are today's only and put this count in the PackageCount text box.

That is how I am interpreting what I've read from the help docs in Access. I'm assuming something is wrong with the syntax because it won't let me use it. Any thoughts would be helpful.

Thanks

 

[pbaldy]

You given form references, but not what should equal them. Also, the form references need to be concatenated into the formula. Here's an untested stab at it, if my eyes are right:

=DCount("*", "tblPackageTracking", "Shipper = '" & Forms!FrmPackageTrackingUPS![Shipper] & "' AND BatchNumber = " & Forms!FrmPackageTrackingUPS![BatchNumber] & " AND ShipDate = #" & Forms!FrmPackageTrackingUPS![Date] & "#")

This presumes that the shipper is a text field, batch number is a number, and date is a date of course. Here's a good reference on how to structure these formulas:

http://www.mvps.org/access/general/gen0018.htm

 

[rem2088]

Will this work if it is going into the form that it is taking the settings out of ? I still can't get this to work and i'm sure it is something syntaxial.

-Ben

 

[pbaldy]

It should work with any open form. Post the syntax you've got now and the data type in the table of all referenced fields. Or a sample db with the form and table.

 

[rem2088]

here is the syntax :


=DCount("*", "tblPackageTracking", "Shipper = '" & Forms!FrmPackageTrackingUPS![Shipper] & "'" AND "BatchNumber = " & Forms!FrmPackageTrackingUPS![BatchNumber] & " AND ShipDate = #" & Forms!FrmPackageTrackingUPS![Date] & "#")

I don't think it is pickinng up the right number. I got it to give me a count, but it is not right.

Shipper is a Text field, BatchNumber is a Number and Date is... a date.

I want the date in the =DCOUNT to be the current date. That is what I suspect is not working

Should I try putting this in for "#" : >=DateAdd("d",0,Date())

-Ben

 

[pbaldy]

You've got the first "AND" outside the quotes. If you want the current date, just use the Date() function. Try this:

=DCount("*", "tblPackageTracking", "Shipper = '" & Forms!FrmPackageTrackingUPS![Shipper] & "' AND BatchNumber = " & Forms!FrmPackageTrackingUPS![BatchNumber] & " AND ShipDate = Date()")

 

[rem2088]

From what I can tell, this is counting all the "UPS" shippers from today. It is not sorting out by the batch number as well. As far as I can tell, it should be. I can't really think of anything that could be causeing this.

-Ben

 

[stepone]

Hi there,

Why not just create a query that gives you the required answer and then do a Dlookup on the query ? I think that will be a lot easier than what you are trying.

Regards,
StepOne

 

[rem2088]

Because then the query will pop up every time the page is refreshed

 

[pbaldy]

It looks to me like it should work, but I can't be sure without data to test on. If you can provide a sample db, we should be able to sort it out.