lblcaption problem

toddw607 · June 21st, 2007, 07:16 AM

  I'm trying to place a label caption on my form using the following statement in VB:
    lblVersion.Caption = Query("SELECT 'Release ' & Major & '.' & Minor & IIf(patch, Chr(96 + patch), '') & IIf(IsNull(Text), '', '-' & Text) & chr(13) & chr(10) & reldate FROM _version;")

     This gives me an error that says "Wrong number of arguments or invalid property assignment"
     Anyone know what I'm doing wrong? Thanks.

robzyc · June 21st, 2007, 07:27 AM

What is the function Query? I cant find it!?!? Is it a custom function?

Why not break the statement out, there is a lot going on there, may make it easier to debug...

Best Regards,
Rob

toddw607 · June 21st, 2007, 07:38 AM

Query is a built in function in Visual Basic

toddw607 · June 21st, 2007, 07:40 AM

Well, I guess it is in the nextgen db. I'll try to find the actual function

toddw607 · June 21st, 2007, 07:41 AM

Here it is:
   'Query() - quick single-item query
' Performs a SELECT query and returns the first field of the first record,
' or Empty if no records are returned by the query.
Function Query(Source As String) As Variant
    Dim DB As Database, rs As Recordset
    Set DB = CurrentDb
    Set rs = DB.OpenRecordset(Source)
    If Not rs.EOF Then Query = rs.fields(0).value
End Function

robzyc · June 21st, 2007, 08:07 AM

ok a few things..

1. Query will return Empty if the rs queried is EOF. So ensure this is handled.
2. The string you have passed is obviously causing you problems, so like i said before, break it out into its components. This is simple debugging procedure. it doesn't need to be all on one line, so break it out.
I can see the problem with the string, but you need to find it, if you do what I said, you will.

I'm not trying to be awkward or unhelpful, what I am trying to do is teach you to debug, a fundamental skill of any developer...

Best Regards,
Rob

toddw607 · June 21st, 2007, 08:12 AM

ok, thanks. I see what I need to do. The strange thing is that this function works with Access 2000 but not with 2003. could this be because Access is more strongly typed than 2000? I have entered a string in this function and it comes out fine in 2003 but not with a SELECT statement. I was wondering if 2003 is strictly looking for a string and not a procedure like SELECT. Coudl that be the problem? I'll try to debug it though. I guess that would be the first step, thanks.

robzyc · June 21st, 2007, 08:22 AM

todd,

A SELECT statement should work fine, but you need to ensure it is a valid SELECT statement.
To point you in the right direction, it is best to translate any information you are fetching (such as paramters)
to a string and then building it up.

For Example:

Code:

Dim d as Date
d = Date
Dim dStr as String
dStr = Format$(d, "mm\/dd\/yyyy")

dim sql as string
sql = "SELECT * FROM [A Table]" & vbCrLf & _
    "WHERE [DateField] = #" & dStr & "#"

Dim rs as DAO.Recordset 'Always Fully-Qualify Database objects as this could be ADO...
set rs = CurrentDB.OpenRecordset(sql)

As you can see, the code may have more lines, but it is easier to read, and where each
part of the string is broken up into its components, its easier to debug, since you can step through and see which part is causing the problems.

Best Regards,
Rob

toddw607 · June 21st, 2007, 08:31 AM

I just wrote a new function:
    Function cQuery(Source As String) As Variant
    Dim DB As Database, rs As Recordset
    Set DB = CodeDb
    Set rs = DB.OpenRecordset(Source)
    If Not rs.EOF Then cQuery = rs.fields(0).value
End Function

  Works fine now. I learned so much more from this than you know. Thanks for your help!

robzyc · June 21st, 2007, 08:46 AM

Strange.. that function looks the same?!?!

Glad its working, and glad you learned something, sorry if I sounded awkward, but the better you get a debugging, the better you get at trapping them before they occur, and being pre-emptive in design!

Best Regards,
Rob