Dlookup alternative

[scotty22]

OK. I've created s sample db with a working query.

Hope this helps.

Kind regards,



Scotty22

[Rawb]

I don't know that there's a way to do what you're wanting in straight Access SQL. In my interweb travels I found something similar but I don't think it would work in your case: In the example I found, the value that was being looked up was always in the same column. You're looking for the name of the column itself.

If you want to check it out though, feel free.

The far easier solution would be to use VBA and a temporary Table. It would also have the speed increase you're looking for.

[scotty22]

I thought as much.

Thanks for your help.

Kind regards,

Scotty22

[scotty22]

Actually, I may need some help for this if possible. I really need to be able to do what I was trying to do before with the DLookup. Any hints on how to perform this through VBA?

Kind regards,

Scotty22

[Rawb]

Sure!

I'll just list the raw basics here, so if you have any more questions, please ask. I'd be happy to help further!

Step 1: Create a Form with spots for the user to enter two Valuation Dates. Add a Command Button to the Form.

Step 2: In the OnClick Event of your Command Button, create a Sub that uses a Recordset (the Recordset is a list of all the Failed_Cols Records matching the valuation dates on your Form). Loop through the Recordset, finding the column name and it's value.

Step 3: Write this information to a temp Table.

Step 4: Display the contents of your temp Table!