DLooup problem

[Liz_I3]

In a access 2000 module I am tring to look up a value fron a table and compare it to my current record that is created from a funcation mostly in SQL.

BxCnt = Dlookup("BoxN", "CntBox", "[StoreID]" = myvar) I get Invalid use of Null. But either the StoreID or myvar is null.

myvar is the current StoreID of the current records from a SQL statment that populates a table.

What I really want to do is get the max count of records for each StoreID. Which I have in my table CntBox. I wanted the dlookup to select the max count from my CntBox table so I could assign that value to a variable in my module that produces a recordset to populate a table.
Thanks

[Andrew Fergus]

Hi

Have you looked at wrapping the entire syntax inside an IIF statement? Like this:

=IIF(len(myvar)=0, 0, DCount(...))

I haven't tested the len() part but there are other ways of testing for Null (if the len method doesn't work) with functions like IsNull or NZ...

Also watch your snytax with the DLookup and DCount functions - the third argument should look like this for a numerical value:

"[StoreID] = " & myvar

or like this for a text value:
"[StoreID] = '" & myvar & "'"

And maybe you want to use a DCount function instead of a DLookup if you want to count the number of records....just a thought....

Andrew

[Liz_I3]

Thanks Andrew
I have been tring the Dlookup, DMax I cant' or don't quite know how to tell it to reference my current position. This is what I have

bxcnt = DMax(" [boxN]", "CntBoxl", "[StoreID]" but it only reference the first records, it won't move to the next records. How do you code if the StoreID is to equal the value from another tables field?

Thanks for your help

L

[Andrew Fergus]

Hi Liz

I'd be happy to help but I'm not understanding the problem 100%. Are you doing this from an existing form? And you want to calculate a 'D' value (e.g. DMax, DCount etc) based on the record you can currently see on the form? And only that record at that point in time? And if you move to a new record on the form, then you want to see a new value based on what you are seeing in the form?

You mentioned earlier this was happening in a module - how is the code triggered? And if there is more to this, do you want to elaborate on what it is you are wanting to do? If you are wanting to count the number of relevant records from another table (as part of a larger process), and unless I'm misunderstanding something, then you should be using the DCount function.

Cheers
Andrew

[Liz_I3]

Hi Andrew
I almost have it working, as it seems to be recongizing my variable.

I used the dcount BxCnt is a integer and myvar is a string
BoxN is a field in the table CntTBox StoreID is a field in the same table
I keep getting a syntex error

BxCnt = DCount("[BoxN]", "CntBox", "[StoreID]= " & myvar)

Yes, it is in a module that is activated from a form. The user enters in a date and press a create button.

Thanks

Liz

[Andrew Fergus]

Hi Liz

I haven't tested this but try this:

BxCnt = DCount("[BoxN]", "CntBox", "[StoreID]= '" & myvar & "'")

Given myvar and I presume StoreID are both string fields, then the myvar part needs to be surrounded with single quotes. If StoreID is not a string field then this won't work. Let me know if this is the case.

Andrew

[Liz_I3]

Thank you very much
Yes I played with it a bit and got it working...

This is what I did

BxCnt = DCount("[BoxN]", "CntBox", "[StoreID]= """ & myvar & """")

I appreciate your time

L

[Andrew Fergus]

Hi Liz

Well done! Nothing quite like doing it yourself - that way you won't forget!! You can also use the single quotes instead of lots of double quotes (per my earlier post). There are also other ways of doing this (e.g. opening a recordset and getting a count that way etc.) but I find using SQL in VBA is pretty powerful and very quick and easy. It's worth spending the time learning what SQL can do in VBA.....

Cheers
Andrew