UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Error 3061, Access 2010    
 
   
habiler
post Dec 20 2017, 04:31 AM
Post#1



Posts: 60
Joined: 6-August 15




Hello
I have a tablbe enfts with two fiels Employeenbr and Birthdate (of child).
I'd know the number of child LT 12 Year a worker have.
With a crossTab i have problems with reports.

As result : 3478 2 (p ex : childrens)
5020 1

CODE
Function ChildLT12YbyPerson(Matr As Long, DateNaiss As Date)

Dim rs3 As DAO.Recordset
Dim strSQL3 As String
Dim CountOfBirthdates As Integer



    strSQL3 = "SELECT EMPLOYEENBR, Count([BIRTHDATE]) AS CountofBirthdates FROM Enfts  WHERE BIRTHDATE >= DateAdd(Y, -12, Date()) and EMPLOYEENBR = " & Matr & " GROUP BY EMPLOYEENBR  "
    Set rs3 = CurrentDb.OpenRecordset(strSQL3)
Debug.Print strSQL3
    With rs3
        If Not (.EOF And .BOF) Then .MoveFirst
        While Not .EOF
         ' While Not LT12Y > 11
    
      ChildLT12YbyPerson = CountOfBirthdates
            .MoveNext
           ' Wend
        Wend
        
        .Close
        Debug.Print "Enfants = :"; Matr, DateNaiss, ChildLT12YbyPerson
    End With
    
    Set rs3 = Nothing
End Function
Go to the top of the page
 
nuclear_nick
post Dec 20 2017, 06:20 AM
Post#2



Posts: 1,471
Joined: 5-February 06
From: Ohio, USA


I noticed that you print to the immediate window (the 'debug.print') your SQL string (strSQL3).

What happens when you copy it from the immediate window and try running it in it's own query? Have you tried it?

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
BruceM
post Dec 20 2017, 07:28 AM
Post#3


UtterAccess VIP
Posts: 7,322
Joined: 24-May 10
From: Downeast Maine


That error (Too few parameters) could be because of the commas in a Debug.Print string. Try:

Debug.Print "Enfants = :"; Matr " & ", " & DateNaiss & ", " & ChildLT12YbyPerson

You mentioned error 3061 in the title, then you said you had problems with reports. What are the problems?
Go to the top of the page
 
habiler
post Dec 20 2017, 09:00 AM
Post#4



Posts: 60
Joined: 6-August 15



The field in my query is null or it's give the error paas-through
Attached File(s)
Attached File  Erreur.JPG ( 29.24K )Number of downloads: 5
 
Go to the top of the page
 
habiler
post Dec 20 2017, 01:36 PM
Post#5



Posts: 60
Joined: 6-August 15



I HAVE THE ERROR MESSAGE 3061 at level DateAdd.

I've tried Date Add with "" or '' and with y,yyyy,Y,YYYY but no result
Go to the top of the page
 
BruceM
post Dec 20 2017, 01:53 PM
Post#6


UtterAccess VIP
Posts: 7,322
Joined: 24-May 10
From: Downeast Maine


The VBA Help file can be useful for syntax problems such as this. To subtract twelve years from the current date you can do:

DateAdd("yyyy", -12, Date())

If that doesn't solve the problem, what exactly are you trying to do? Is Birthdate a date/time field? If not, it should be, but sometimes you can use CDate to convert the value:

CDate(Birthdate) >= DateAdd("yyyy", -12, Date())

Also, I mentioned a possible problem with one of your Debug lines of code. Can you compile the code? Did you try the change I suggested?
Go to the top of the page
 
habiler
post Dec 20 2017, 03:30 PM
Post#7



Posts: 60
Joined: 6-August 15



Hello

The syntax "yyyy" is refused.

""yyyy"" is "accepted" but error 3061 away
Go to the top of the page
 
nuclear_nick
post Dec 21 2017, 06:52 AM
Post#8



Posts: 1,471
Joined: 5-February 06
From: Ohio, USA


Did you debug.print the SQL and run it on its own yet? That should help with any syntax issues.

Like, for instance, why ""yyyy"" worked and "yyyy" didn't. It's because you're using SQL, as a string, as the argument to open the recordset. A string already has quotes around it ("This is a string"), so when you have a string inside of a string, you need to handle it carefully.

So you know this...
CODE
"... DateAdd("yyyy", -12, Date()) ..."

... doesn't work, because of the starting and stopping of the string. If you were to debug.print the string, like you were already doing, and could decipher the resulting string (your SQL statement), you should have noticed it was not going to work, and if you pasted the SQL into a new query and tried to execute it, you also would have seen it 'not work'.

So you need to make the string come out correctly. Most of the time you can also use single quotes, like so...
CODE
"... DateAdd('yyyy', -12, Date()) ..."

Note the single quotes around the "yyyy" parameter of DateAdd.

Personally, I would set such things as a variable before building the SQL.
CODE
Function ChildLT12YbyPerson(Matr As Long, DateNaiss As Date)

Dim rs3 As DAO.Recordset
Dim strSQL3 As String
Dim CountOfBirthdates As Integer
Dim CheckBirthDay As Date   '<---- new variable

  CheckBirthDay = DateAdd("yyyy",-12,DateNaiss)  '<---- check on date parameter (DateNaiss), not current, maybe?

'change SQL to put in date variable instead of DateAdd function...
  strSQL3 = "SELECT EMPLOYEENBR, Count([BIRTHDATE]) AS CountofBirthdates FROM Enfts  " &  _
    "WHERE BIRTHDATE >= #" & CheckBirthDay & "# AND EMPLOYEENBR = " & Matr & " GROUP BY EMPLOYEENBR "

    Set rs3 = CurrentDb.OpenRecordset(strSQL3)

Debug.Print strSQL3

'... <rest of code here>...      

End Function


Hope that helps...

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
BruceM
post Dec 21 2017, 07:37 AM
Post#9


UtterAccess VIP
Posts: 7,322
Joined: 24-May 10
From: Downeast Maine


As for error 3061, I am running out of ways to say this: the problem is most likely this line:

Debug.Print "Enfants = :"; Matr, DateNaiss, ChildLT12YbyPerson

Either change it as I suggested, commment it out (place an apostrophe at the start of the line), or delete the line, then try the code again.

Compiling the code should have identified the problem. Did you compile?

Also, if your default date format is something other than mm/dd/yyyy (or mm/dd/yy, etc.), or yyyy/mm/dd, you may need to do something like this (building on Nick's code):

"WHERE BIRTHDATE >= #" & Format(CheckBirthDay,"mm\/dd\/yyyy") & "# AND EMPLOYEENBR = " & Matr & " GROUP BY EMPLOYEENBR "

or

"WHERE BIRTHDATE >= #" & Format(CheckBirthDay,"yyyy\/mm\/dd") & "# AND EMPLOYEENBR = " & Matr & " GROUP BY EMPLOYEENBR "
Go to the top of the page
 
habiler
post Dec 22 2017, 01:40 AM
Post#10



Posts: 60
Joined: 6-August 15



Hello everybody,

But now it works with => DateAdd('yyyy', -12, Date()).

Thanks

Habiler
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th April 2018 - 06:59 PM