Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
Hi everybody,
I would appreciate some hint to resolve a problem concerning some SQL code that runs without problem in a query design, however using the same code (with exception of inserting concatenation characters to accommodate line breaks) in VBA a Run-time error ‘3141’ is generated.
The error goes like that:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
The message is not exactly meaningful because basically the error could be generated by any part of the code.
The code suppose to find a total member count for the ones under the age of 10 years of age.
Public Sub AgeCalc()Dim Members_2007 As Database
Dim Members As RecordsetSet Members_2007 = CurrentDB()
Set Members = Members_2007.OpenRecordset("Members")Members.OpenRecordset
Members.MoveFirstDim Age10 As Integer
Age10 = 0
Dim strSQL1 As StringstrSQL1 = "SELECT Int((Date()-[Members.DOB])/365.25) AS Age10" & _
"FROM Members" & _
"WHERE (((Int((Date()-[Members.DOB])/365.25))<10));"
Debug.Print "Total Members = "; Members.RecordCount
DoCmd.RunSQL strSQL1End Sub
Well, let's see how VBA puts your SELECT query together, shall we?
SELECT Int((Date()-[Members.DOB])/365.25) AS Age10FROM MembersWHERE (((Int((Date()-[Members.DOB])/365.25))<10));
Put a space before the end quote.
I.E. ...AS Age10 " & _
...Members " & _
0 
Hello Razor2.3
Many thanks for your very fast response. I tried your suggestion, however, I am now getting a Run-time error ‘2342”,
A RunSQL action required an argument consisting an SQL statement.
Modifying the code to a one liner only produces the same result.
Regards, Wilfried
0
Hello Razor2.3
Your advice certainly pointed me to something I generally did not think about. Thanks for that.
I just did a bit more digging and found that the 'Run SQL' command only applies to action queries, i.e. not to a Select Query, which I am trying to use.
It seems I have to work on a different approach.Regards, Wilfried
0
Hi Razor2.3
Don't really know the difference between ADO and DAO, I am at an entry level. However, I have setup the DB 'as DAO.Database' as well as the recordset as DAO.Recordset.
Regards, Wilfried
0
Personally I prefer ADO. I find it easier to use, and it's the successor to DAO.
That said, if you want to run your SQL, you need to do something like this:
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT Int((Date()-[Members.DOB])/365.25) AS Age10 " & _
"FROM Members " & _
"WHERE (((Int((Date()-[Members.DOB])/365.25))<10));"Set rs = urDB.OpenRecordset("strSQL", dbOpenDynaset)
EDIT: Then again, you've already pulled the entire table. You might as well just pragmatically walk though the records, looking for what would be returned.
0
Hi Razor2.3
Thanks for your advice. I sort off worked out that SQL string code can only be processed in VBA if this code adds, deletes or modifies records, selecting records is not one of them.
After lengthy stuffing around I found the following way:
Public Function Age20_AM() As Integer
Dim FTC_Membership_2007_08 As DAO.Database
Dim Members As DAO.RecordsetSet FTC_Membership_2007_08 = CurrentDB()
Set Members = FTC_Membership_2007_08.OpenRecordset("Select * from Members " & _
"WHERE (((Int(Date()-[DOB])/365.25)>9) and (Int((Date()-[DOB])/365.25) <20)" & _
"and [Sex] = 'M' and [Type of Membership] Like '*Adult*')")
Members.MoveLastIf Members.RecordCount = 0 Then
Age20_AM = 0
Else
Age20_AM = Members.RecordCount
End IfEnd Function
I use this code to find out total member counts in different age groups, i.e. 10-20, 30-40 etc.
I noticed that a report would not accept data derived from a procedure, only from a function. So, the above works to some extend.
Now, some RecordCounts are correct, others aren’t. I have not worked out yet, why. I am using the same code, only slightly modified to account for different age details etc.That’s one thing, the other is if there are no records for one age group, the code returns an error.
I thought working around this error with the if else endif combination above, but that does not do anything.
So, I am a bit further, but still ‘working on it’. I am enjoying it though.
Regards, Wilfried
0
...the code returns an error...
So you'll have to evoke On Error Resume Next. Careful, though; that's one line of code that'll give you nothing but headaches.
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
