Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 21st, 2007, 07:16 AM
Authorized User
 
Join Date: Feb 2007
Posts: 88
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to toddw607 Send a message via Yahoo to toddw607
Default lblcaption problem


  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.

 
Old June 21st, 2007, 07:27 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

 
Old June 21st, 2007, 07:38 AM
Authorized User
 
Join Date: Feb 2007
Posts: 88
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to toddw607 Send a message via Yahoo to toddw607
Default

Query is a built in function in Visual Basic

 
Old June 21st, 2007, 07:40 AM
Authorized User
 
Join Date: Feb 2007
Posts: 88
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to toddw607 Send a message via Yahoo to toddw607
Default

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


 
Old June 21st, 2007, 07:41 AM
Authorized User
 
Join Date: Feb 2007
Posts: 88
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to toddw607 Send a message via Yahoo to toddw607
Default

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

 
Old June 21st, 2007, 08:07 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

 
Old June 21st, 2007, 08:12 AM
Authorized User
 
Join Date: Feb 2007
Posts: 88
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to toddw607 Send a message via Yahoo to toddw607
Default

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.

 
Old June 21st, 2007, 08:22 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 21st, 2007, 08:31 AM
Authorized User
 
Join Date: Feb 2007
Posts: 88
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to toddw607 Send a message via Yahoo to toddw607
Default

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!

 
Old June 21st, 2007, 08:46 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

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










Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.