Solved Dlookup in Query

[Sarah.M]

Hi, Plz note, My VBA is blocked by IT for security reason.
I can use only Expression builder, Macros, Queries, Tables, Forms and Reports only.
------------------------------------------------
I am trying to practice Dlookup, but it dose not work with me I want Read to be shown in the query in front of each record but it dose not work
I heard NZ does not work with Office 64 bit, If I remove NZ I got error Dlookup Undefined function

plz help me!

Sample Attached

 

[arnelgp]

no problem on my msa running your query.

 

[cheekybuddha]

Describe table ReadT: field names, datatype, indexes, relationships.

 

[MajP]

I am trying to practice Dlookup, but it dose not work with me I want Read to be shown in the query in front of each record but it dose not work
I heard NZ does not work with Office 64 bit, If I remove NZ I got error Dlookup Undefined function

Those types of errors sound like a reference issue with VBA. Now in truth you should definitely not be using Dlookup here in a query. Very inefficient and more difficult. Just do a simple join to the ReadingT table and pull in Read.

I would create a new database which will ensure you have the current references, and import your tables into there. See if that fixes anything. I have never heard of issues with NZ in 64 bit. Sounds like an urban myth.

 

[The_Doc_Man]

Usually you will not have errors with NZ, but a little-appreciated fact is that if there is another failing library reference AND it appears in the reference list before the one that contains NZ, that would cause NZ to fail. In that case, NZ's library isn't the problem - it is a prior broken reference in the list of references. However, that should be the least of your worries because of the other things ALSO in the library that contains NZ. I would be surprised if NZ was the first such problem you ran into.

@Sarah.M - you keep on telling us that your VBA is blocked for security reasons. The issue for that blockage isn't clear, so here is my question (and I am trying to be constructive, so bear with me).

WHY is the IT group blocking your VBA? You need to know that so you can address their concerns. Blocking VBA affects well over half of the programs in the MS Office suite and, while it doesn't make them brain-dead, DOES limit their abilities significantly.

Is this Access project your idea or a response to a boss's request? When I had a similar IT restriction problem before, I went to my boss and pointed out that the IT staff was in the way and was preventing me from making headway. I told the IT person who was responsible for the blockage that I was building something at the request of a higher-up and that from now on, HE would be called when I failed to make something happen. It is typical of IT folks to go overboard, but by blocking Office VBA, you make it impossible to do certain things that are typically desirable in an office. Sometimes, your best solution isn't to work around a blockage but to clear the blockage. Technical solutions go only so far no matter how big, but small political solutions can often make a big difference.

 

[Sarah.M]

Usually you will not have errors with NZ, but a little-appreciated fact is that if there is another failing library reference AND it appears in the reference list before the one that contains NZ, that would cause NZ to fail. In that case, NZ's library isn't the problem - it is a prior broken reference in the list of references. However, that should be the least of your worries because of the other things ALSO in the library that contains NZ. I would be surprised if NZ was the first such problem you ran into.

@Sarah.M - you keep on telling us that your VBA is blocked for security reasons. The issue for that blockage isn't clear, so here is my question (and I am trying to be constructive, so bear with me).

WHY is the IT group blocking your VBA? You need to know that so you can address their concerns. Blocking VBA affects well over half of the programs in the MS Office suite and, while it doesn't make them brain-dead, DOES limit their abilities significantly.

Is this Access project your idea or a response to a boss's request? When I had a similar IT restriction problem before, I went to my boss and pointed out that the IT staff was in the way and was preventing me from making headway. I told the IT person who was responsible for the blockage that I was building something at the request of a higher-up and that from now on, HE would be called when I failed to make something happen. It is typical of IT folks to go overboard, but by blocking Office VBA, you make it impossible to do certain things that are typically desirable in an office. Sometimes, your best solution isn't to work around a blockage but to clear the blockage. Technical solutions go only so far no matter how big, but small political solutions can often make a big difference.

Hi, IT told me some of Hackers can us VBA to harm PCs, so they are blocking vba
How can I find list of references in microsoft access? located

 

[cheekybuddha]

Describe table ReadT: field names, datatype, indexes, relationships.

Do this and we can probably show you how to write the same query without VBA

 

[cheekybuddha]

OK, I got back to a computer and could download your db.

Why in your expression for Read in ReadDlookupQ are you looking for ReadT.ReadID = MeetingT.MeetingID ?

Your relationships define the join on ReadT.MeetingID = MeetingT.MeetingID.

It's not really clear what your are trying to query for.

 

[Sarah.M]

Do this and we can probably show you how to write the same query without VBA

I apologize, I did not understand your request, could you explain to me more, so that I can answer?

 

[cheekybuddha]

I apologize, I did not understand your request, could you explain to me more, so that I can answer?

See Post #8

 

[Sarah.M]

Your relationships define the join on ReadT.MeetingID = MeetingT.MeetingID.

Nz(DLookUp("[ReadT]![Read]","[ReadT]"," [ReadT]![MeetingID]=" & [MeetingT]![MeetingID]))
I got this -1, why checkbox does not show?

 

[Sarah.M]

Your relationships define the join on ReadT.MeetingID = MeetingT.MeetingID.

First, thank you for your patience and help, is this what you want me to do?
Nz(DLookUp("[ReadT]![Read]","[ReadT]"," [ReadT]![MeetingID]=" & [MeetingT]![MeetingID]))

 

[The_Doc_Man]

Hi, IT told me some of Hackers can us VBA to harm PCs, so they are blocking vba
How can I find list of references in microsoft access? located

A vague "some hackers can use VBA to harm PCs" is equally grounds for them to completely disable ANY utility that has ANY programming ability at all, plus features in Windows itself. A decent security package with a heuristic component that detects specifically dangerous touches would work better than their approach. They are giving you a lazy-person's excuse for IT blocking something they don't want to face. It is the old "kill a mosquito with a sledge-hammer" approach. A non-specific reason.

 

[cheekybuddha]

First, thank you for your patience and help, is this what you want me to do?
Nz(DLookUp("[ReadT]![Read]","[ReadT]"," [ReadT]![MeetingID]=" & [MeetingT]![MeetingID]))

This would make sense, but the data in table ReadT seems odd! IT looks like you populated this table with a cartesian query InsertToReadT.

If the data is what you intended, then you can replace your DLookup query with:

SELECT
  m.*,
  r.Read
FROM MeetingT m
LEFT JOIN ReadT r
       ON m.MeetingID = r.MeetingID;

 

[cheekybuddha]

Using the query I posted, my version of Access shows the checkboxes:

 

[Sarah.M]

This would make sense, but the data in table ReadT seems odd! IT looks like you populated this table with a cartesian query InsertToReadT.

If the data is what you intended, then you can replace your DLookup query with:

SELECT
  m.*,
  r.Read
FROM MeetingT m
LEFT JOIN ReadT r
       ON m.MeetingID = r.MeetingID;

@cheekybuddha It works Thanks!!!

 

[Sarah.M]

See Post #8

How do you make this Post #8 Clickable? #8

 

[Sarah.M]

Using the query I posted, my version of Access shows the checkboxes:
View attachment 99827

Thanks for help

 

[cheekybuddha]

 

[GPGeorge]

A vague "some hackers can use VBA to harm PCs" is equally grounds for them to completely disable ANY utility that has ANY programming ability at all, plus features in Windows itself. A decent security package with a heuristic component that detects specifically dangerous touches would work better than their approach. They are giving you a lazy-person's excuse for IT blocking something they don't want to face. It is the old "kill a mosquito with a sledge-hammer" approach. A non-specific reason.

To me, it almost sounds like this IT department has come up with an excuse to marginalize Access by limiting its usability. That's beyond lazy, if it were true. Of course, that means they also cripple other Office applications, albeit to a lesser extent. If I had to work in such a hostile environment, I wouldn't. The only use I would make of any Office application would be to update my resume in Word....