SQL in VBA not working, dunno Why

Access_guy49

Registered User.
Local time
Today, 17:40
Joined
Sep 7, 2007
Messages
462
Ok, in a nutshell, i'm working with a form where i have a listbox. and based on a button the user selects, i want the listbox to populate with something. So I'm setting the rowsource to be an SQL statement, and then from there doing a requery.

My SQL statement that works in a query is:

Code:
SELECT Schools.NAME, tbl_SchlPrgms.Date, tbl_SchlPrgms.Loc, tbl_SchlPrgms.StdntNm, tbl_SchlPrgms.Rev, tbl_Link_schoolPrograms.Grades, tbl_Program.Name
FROM (Schools INNER JOIN tbl_SchlPrgms ON Schools.SchlID = tbl_SchlPrgms.Schl_ID) INNER JOIN (tbl_Program INNER JOIN tbl_Link_schoolPrograms ON tbl_Program.ProgID = tbl_Link_schoolPrograms.ProgramID) ON tbl_SchlPrgms.ID = tbl_Link_schoolPrograms.SchlPrgID
WHERE (((Format([Date],"yyyy"))=[Enter Year]));


My VBA is:

Code:
MySQL = "SELECT Schools.NAME, tbl_SchlPrgms.Date, tbl_SchlPrgms.Loc, tbl_SchlPrgms.StdntNm, tbl_SchlPrgms.Rev, tbl_Link_schoolPrograms.Grades, tbl_Program.Name FROM (Schools INNER JOIN tbl_SchlPrgms ON Schools.SchlID = tbl_SchlPrgms.Schl_ID) INNER JOIN (tbl_Program INNER JOIN tbl_Link_schoolPrograms ON tbl_Program.ProgID = tbl_Link_schoolPrograms.ProgramID) ON tbl_SchlPrgms.ID = tbl_Link_schoolPrograms.SchlPrgID WHERE (((Format([Date]," & "''" & "yyyy" & "''" & "))=[" & MyYear & "]));"

MyYear is a string variable which is set with an inputbox.
When i set the rowsource and then requery, Nothing is displayed... any ideas would be appreciated
 
Remove the square brackets around MyYear.
And put square brackets around DATE and NAME (both are Access reserved words and should NOT be used as names for fields or objects).
 
I'm still getting nothing. My complete code that I am currently using is:

Code:
Private Sub Command3_Click()
    'Make the list box display the school programs information
    Dim MySQL As String
    Dim myYear As String
    
    myYear = InputBox("Enter Year")
    
    MySQL = "SELECT Schools.[NAME], tbl_SchlPrgms.[Date], tbl_SchlPrgms.Loc, tbl_SchlPrgms.StdntNm, tbl_SchlPrgms.Rev, tbl_Link_schoolPrograms.Grades, tbl_Program.[Name] FROM (Schools INNER JOIN tbl_SchlPrgms ON Schools.SchlID = tbl_SchlPrgms.Schl_ID) INNER JOIN (tbl_Program INNER JOIN tbl_Link_schoolPrograms ON tbl_Program.ProgID = tbl_Link_schoolPrograms.ProgramID) ON tbl_SchlPrgms.ID = tbl_Link_schoolPrograms.SchlPrgID"
    MySQL = MySQL & " WHERE (((Format([Date]," & "''" & "yyyy" & "''" & "))=" & myYear & "));"
    
    MsgBox MySQL
    
    
    Me.Lst_Results.RowSource = MySQL
    Me.Lst_Results.ColumnCount = 7
    Me.Lst_Results.Requery

End Sub
 
Square brackets around the first Date field?

You may also need to Format MyYear
 
Try changing this line:

MySQL = MySQL & " WHERE (((Format([Date]," & "''" & "yyyy" & "''" & "))=" & myYear & "));"

to this:

MySQL = MySQL & " WHERE (((Year([Date])))=" & CInt(myYear) & "));"
 
I thought i had the square brackets around the first date field?? am i missing one.

SOS - That didn't work either.. :( i have a fear that i'm gonna be out of luck shortly.

Should I change the field names to non-reserved words?? would that help?
 
When I first looked at it it was without the brackets.

Using SOS' suggestion, you might also need to specify which table the [Date] field is in in the WHERE clause.

WHERE Year(SchlPrgms.[Date]) = " & Year(MyDate) & ";"
 
VBAInet - I added your suggestion and it STILL didn't work, so I took a really close look at the brackets and there was one too many

MySQL & " WHERE (((Year(tbl_SchlPrgms.[Date])))=" & CInt(myYear) & "));"

Deleted that Red one, and now it works like a charm!
Thank you both soooo much! i was bangin my head on the wall about this one for the entire morning!
 
Fyi: Next time you run the InputBox, don't enter anything in the box and click Cancel. See the outcome.
 
HMMM.. type mismatch..
So i'm thinking an error catch would be a good idea hu?
 
It returns Null. Test using IsNull() then prompt, or Nz() the result.
 
When i hit Cancel the value of MyYear turns out to be "", not null. is that unusual?
 
That's my error there. It does actually return a zero-length string. So you can use Len() in that case.
 
I never use the InputBox() so I keep forgetting whether it's a Null or zls :D

Glad we could help.
 
OK, Question, I was going to be using the list box to tally some numbers.
The data displayed has how many people attended an event. so i was going to have some code that just runs down the list box and added them up. my problem is this...

The powers that be, collect the data as a TOTAL per event, but they want to keep track of which grades attended as a seperate thing. So basically I have a one to many relationship.

if 40 grade 5's and 40 Grade 6's come to an event, (because they are at different times) they are seen as two records... BUT on the main record for the daily event, that is where the number attended is listed as 80. So with my list box that i created, I will have the parent record repeating 2 or 3 times. and i don't want 80*3 I just want 80 and then on to the next event.

I was thinking that I can make another list box, that I set the row source and don't add the child records and then just tally that list.. but i'm starting to think there is a better way to do this.. ???
 
Ya, but distinct wont work because they are combined. so the records themselves are distinct even though the parent aspect of the record is being duplicated.

Parent record -- Child Record

Class1, Jan 12, 80 kids -- Grade 4, snowshoeing
Class1, Jan 12, 80 kids -- Grade 3, Trapping.
Class2, Jan 20, 50 kids -- Grade 6, Inside lesson
Class2, Jan 20, 50 kids -- Grade 5, Outside lesson

Above is an example, there is only one parent record, but that day had several events. and unfortunatly they don't keep track of kids per event, only kids per the Day.
 
The whole idea is vague to me because I can't see the database or know your relationships. You could create a new thread re this with some screen shots or even a sample database.
 

Users who are viewing this thread

Back
Top Bottom