By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,160 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

SQL select query in function to return result for use as variant

P: 11
I am trying to write a function within VB module in Access that will select using SQL query one value from many that will then be used as a variant within that function.

The example.

Maintenance Table contains ID, ItemID, MaintainDate, OK

each Item may have multiple entries in this maintenance table.

I would like a function to lookup the latest maintenance date for a particular item ID, returning one value, a date.

The problem is trying to get the function to use that result as a variant, since I want to determine whether it is a). null (no maintenance entry yet), or b). before a certain date (will return different values from the function)

any help gratefully received, I've learnt a lot from this forum before, cheers.

Phil
Sep 20 '07 #1
Share this Question
Share on Google+
2 Replies


Denburt
Expert 100+
P: 1,356
I'm not sure I understand the question, you should really give it a go (sounds like you did) then post some code that your working from. Most people on almost any forum would rather see that your making an attempt (by posting some relevant code) and not just trying to get someone to do it for you. You will find you threads get quicker responses because more people will have a better understanding of what your doing and they won't think hey he's trying to get me to do it.

Here is a quick one though, lets see if I understand (I am sure I am off but you tell me how far). This is also off the top of my head and I am in a hurry to get outa here so bear with me.

Expand|Select|Wrap|Line Numbers
  1. Function KissOff(MyID) as Variant
  2. Dim db as dao.database
  3. Dim rs as dao.recordset
  4. set rs  = db.openrecordest("Select Max(MaintainDate)  MaxMaint from Maintenance  Where ID =" & MyID)
  5. KissOff = rs!MaxMaint
  6.  
Does this help?
Sep 20 '07 #2

ADezii
Expert 5K+
P: 8,616
I am trying to write a function within VB module in Access that will select using SQL query one value from many that will then be used as a variant within that function.

The example.

Maintenance Table contains ID, ItemID, MaintainDate, OK

each Item may have multiple entries in this maintenance table.

I would like a function to lookup the latest maintenance date for a particular item ID, returning one value, a date.

The problem is trying to get the function to use that result as a variant, since I want to determine whether it is a). null (no maintenance entry yet), or b). before a certain date (will return different values from the function)

any help gratefully received, I've learnt a lot from this forum before, cheers.

Phil
I'm a little confused like Denburt, also but it seems as though your problem is with Null Values. Remember, only Variant Data Types can contain Null values so you can try setting the Return Value of the Function to Variant, or use the Type Conversion Function CVar(<expression>) to coerce an Expression or Value to a Variant Type.
Sep 20 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.