OpenRecordset 2

[beckyh]

How do I return the result of this SQL statement. I want to pass the value to a module.

sql = "SELECT COUNT(QuoteItemNumber) AS CountOfQuoteItemNumber FROM QuoteHeader GROUP BY QuoteNumber HAVING (QuoteNumber = 'q-021305-799')"

Set rs = db.OpenRecordset(sql)

 

[PHV]

The value is rs(0)

You may also consider the DCount function:
DCount("*", "QuoteHeader", "QuoteNumber='q-021305-799'")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[beckyh]

The value of the SQL statement is 7. I want to pass that value. Not 0. Is there a way to pass 7?

 

[beckyh]

Nevermind. The Dcount worked like a charm!

 

[grande]

Set rs = db.OpenRecordset(sql)

With rs
   intWhatever = .Fields("CountOfQuoteItemNumber")
End With

Is that what you mean? Actually, a shorter way of doing that is:

intWhatever CurrentDb.OpenRecordset(sql).Fields("CountOfQuoteItemNumber")

 

[grande]

Crap, I mean

intWhatever = CurrentDb.OpenRecordset(sql).Fields("CountOfQuoteItemNumber")

Sorry, forgot the =.

 

[beckyh]

PHV,
When I changed it to

count = DCount("*", "QuoteHeader", "QuoteNumber='[Forms]![Issue Quote Form]![QuoteNumber]'")

it doesn't work any longer. Can't I call the number from the Issue Quote Form??

I get an error saying "This property is read-only and can't be set.

 

[PHV]

myCount = DCount("*", "QuoteHeader", "QuoteNumber='[Forms]![Issue Quote Form]![QuoteNumber]'")

Count is a reserved word.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Sometimes you'll need to concatenate the value into the criterion, not the reference:

[tt]myCount = DCount("*", "QuoteHeader", "QuoteNumber='" & [Forms]![Issue Quote Form]![QuoteNumber] & "'")[/tt]

Roy-Vidar

 

[beckyh]

Thank you RoyVidar! Worked great!!!!!!!