finding an empty record

[ClaraBarton]

I want to find a record that meets several criteria.

SELECT tblLocation.*, tblLocation.fItemID, tblLocation.InUse, tblLocation.Parent
FROM tblLocation
WHERE (((tblLocation.fItemID) Is Null) AND ((tblLocation.InUse)=False) AND ((tblLocation.Parent)="DR2"));

This query returns 15 available locations (records).
I want to update only one with a new ItemID and set inUse to true.
How do I return only one record with the lowest ID (locID)?

 

[Gasman]

Use DMin() on that field?

DMin, DMax Functions - Microsoft Support

support.microsoft.com

Alternatively, perhaps Top 1 and Descending on that field?

 

[ClaraBarton]

Ok, I asked the wrong question. I'm using this in a module::

strFind = "[InUse] = 0 And nz([ItemID],0) = 0 and [Parent] = """ & strParent & """ "
    lngLoc = Nz(DLookup("LocID", "tblLocation", strFind), 0)

How do I add a group to a where string?

 

[jdraw]

Tell us more about your "group" requirement

 

[ClaraBarton]

Well, in the query DMin requires them all to be grouped.
I've tried this:
lngLoc = Dmin("LocID", "tblLocation", "Nz(DLookup("LocID", "tblLocation", strFind), 0)")")
but it doesn't compile

 

[Gasman]

No it does not, you just need to match locid = the dmin value.
I believe however the Top 1 descending would be quicker?

 

[ClaraBarton]

OKaaa.... What would that look like in the above string?

 

[ClaraBarton]

I don't care it it's the smallest I just want to pick one.

 

[Gasman]

You adjust to suit.

SELECT Top 1 tblDaily.*
FROM tblDaily order by dailyID Desc

 

[theDBguy]

I don't care it it's the smallest I just want to pick one.

DLookup() should pick one for you.

 

[ClaraBarton]

theDBguy... really? Ok, problem must be elsewhere. Thank you.

 

[Pat Hartman]

This is a two step process. Use dMin() with your criteria to find the PK of the lowest record that matches the criteria.
Then use an update query that picks the selected record and updates the ItemID and InUse flag.

FYI, flags are usually unnecessary. I don't know your situation obviously but usually a date works better. That not only tells you that something is in use but when it obtained that status.

 

[ClaraBarton]

This is a database for file drawers. Some drawers have divisions and folders. Some are completely numerical. As things get removed, the address stays but it becomes available for use. I want to locate the proper drawer, etc and use any old locations not being used or create new locations if none are available. An old location will have inuse = false and also contain no item number. Sounds very simple but it's been an ongoing work-in-progress for a long time. Then they showed up with ItemGenie and I needed to steal some ideas from there. Just never quite finished. Thanks for your response.

 

[Gasman]

If you are not bothered about the locid, then a DlookUp() will find the first one.

 

[Pat Hartman]

inuse = false and also contain no item number.

basic violation of first normal form. The presence or absence of an Item number identifies whether or not the drawer is in use. When you have two pieces of data like this, you have to be conscientious in your programming to keep them in sync and you CANNOT leave this to the user. What if the item number is not null but the in use is false? Just to satisfy your curiosity, run a query that looks for that condition to see if you currently have bad data stored. Don't forget to check for ZLS strings also if you allowed them in this text field. You can't tell the difference between null and ZLS just by looking. You need to select/exclude both in your queries.

At least this is a case where bad data of one type at least can be identified easily and rectified by manual actions. Storing only Item Number only and NOT the flag, which is redundant, minimizes the potential for conflict.

 

[Pat Hartman]

If you are not bothered about the locid, then a DlookUp() will find the first one.

It will return the first record the query engine finds that meets the criteria. The OP specifically requested the lowest number. Seems like random is OK but the choice of dMin() or dLookup() is contingent on the other business rules. dMin() returns the lowest ID. dLookup() returns a random ID.

 

[Gasman]

It will return the first record the query engine finds that meets the criteria. The OP specifically requested the lowest number. Seems like random is OK but the choice of dMin() or dLookup() is contingent on the other business rules. dMin() returns the lowest ID. dLookup() returns a random ID.

And then in post #8 she said she is not bothered which one, just anyone that meets the criteria?
So I have I believe answered both her requests?