Help with this SQL statement into VBA

[robtyketto]

Need some help with some SQL below to use as an event for a combo box.

Select c.Coach_Id, c.coach_first_name, c.coach_second_name
from Coach c, coachrates cr
where c.coach_ID = cr.coach_ID
and cr.sport_ID = me.comboSport.value
and weekday(me.comboDate.value) in
(
Select day
from coach_Availability
and me.comboStarttime.value = time
)

me.comboDate.value = dd/mm/yy
me.comboStarttime.value = hh:mm

I have used SQL on other database packages, with MS Access syntax being different (never having to specifiy the join type) its sometimes difficult to test as I cant run this as creating this as a query (or can I, me.combo.value will just be accepted as parameters?)

Can anyone help me out?
Anyone recommend how to test SQL code that use values on form like here im using combo boxes?

Thought I could just somehow pass them in as variables.
Thanks.

[MMcCarthy]

What is the logic of this?

 
WHERE cr.sport_ID = [Forms]![FormName]![comboSport]
and weekday([Forms]![FormName]![comboDate]) in
(
Select day 
from coach_Availability 
WHERE [time] = [Forms]![FormName]![comboStarttime]
)
 

I have used SQL on other database packages, with MS Access syntax being different (never having to specifiy the join type) its sometimes difficult to test as I cant run this as creating this as a query (or can I, me.combo.value will just be accepted as parameters?)

Access does use joins INNER, LEFT and RIGHT. No join implies a full outer join.

Try this for the join...

SELECT c.Coach_Id, c.coach_first_name, c.coach_second_name
FROM Coach c INNER JOIN coachrates cr 
ON c.coach_ID = cr.coach_ID
 

[NeoPa]

What is the logic of this?

WHERE cr.sport_ID = [Forms]![FormName]![comboSport]
and weekday([Forms]![FormName]![comboDate]) in
(
Select day 
from coach_Availability 
WHERE [time] = [Forms]![FormName]![comboStarttime]
)

This is saying :
Include only those values where the (named) form's comboDate field matches any one of the values in the subquery.
The WHERE clause of the subquery, though, should probably be formulated something like

WHERE [time] Between [Forms]![FormName]![comboStarttime] And [Forms]![FormName]![comboEndtime]

though, assuming a time RANGE is required rather than simply those where it exactly matches the start time.

[MMcCarthy]

This is saying :
Include only those values where the (named) form's comboDate field matches any one of the values in the subquery.

But outside of the first line how is it connected to the original query?

[NeoPa]

It's the last part of the WHERE clause.
Does that answer your question (I'm not sure I understand it correctly)?
In the mean-time - I'll do some tests to ensure this IS valid SQL - I think it is.

[MMcCarthy]

It's the last part of the WHERE clause.
Does that answer your question (I'm not sure I understand it correctly)?
In the mean-time - I'll do some tests to ensure this IS valid SQL - I think it is.

Maybe it's just my Brain.

The last part of the where statement says
"and where day of value in combo box is in nested select statement"

Neither of these is connected in any way that I can see to the original query so how can they affect the results?

Is it just me?

Mary

[NeoPa]

No Mary, it's not just you.
But, I think it's valid anyway, if a little obscure to understand why.
To explain, consider the case where, due to the selection of comboStarttime, there are no valid slots available, this WOULD (correctly) have the effect that no items would be listed in the query either.

So, I suppose you could say the dependent data for the query is not just the underlying table, but also the selection in the combo boxes (It's actually quite clever really).

BTW the subquery in the WHERE clause does work.
In the design grid it shows up in the Criteria section as 'In (SELECT blah blah blah)'.

[NeoPa]

Another way to look at it is :
SELECT blah blah blah
FROM blah
WHERE (other where clause data)
...but only show any lines at all if the comboDate and comboStarttime combination produces valid results from [coach_Availability].

[MMcCarthy]

No Mary, it's not just you.
But, I think it's valid anyway, if a little obscure to understand why.
To explain, consider the case where, due to the selection of comboStarttime, there are no valid slots available, this WOULD (correctly) have the effect that no items would be listed in the query either.

So, I suppose you could say the dependent data for the query is not just the underlying table, but also the selection in the combo boxes (It's actually quite clever really).

BTW the subquery in the WHERE clause does work.
In the design grid it shows up in the Criteria section as 'In (SELECT blah blah blah)'.

I wasn't really worried about the use of the sub query just that it is normally used to compare a value in the original query to a value in the sub query.

I get your explanation about the query not returning any values if there is no valid day available in the sub query based on the value in the combo box. It is clever and I hope that it's what the poster intended.

Mary

[NeoPa]

That's a good point.
Maybe you could post a confirmation of your cleverness robtyketto ;).

[robtyketto]

Ive just woke up, thanks for the syntax corrections Im'm just about to try it.

The reason behind my structuring of the SQL is from techniques I used at an old job when I was writing code for the database package 'INGRES' I was trained in-house and saw this techinique used alot.

INGRES SQL was more user friendly and didnt have to specify the JOIN type thats why I struggle with the syntax in MS ACCESS.

Since then I left my job to become student.
Will post back later :)

[robtyketto]

Ok, so after learning alittle more of the syntax of VBA I created this below, assigned it to an event and now get a syntax error :(

"The expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to variables."

Me.comboCoach.RowSource = "Select c.Coach_Id, c.Coach_first_name, c.Coach_last_name " & _
"FROM Coach c INNER JOIN Coach_rates cr ON c.Coach_ID = cr.Coach_ID " & _
"WHERE cr.Sport_ID = " & Me.comboSport.Value & _
"AND weekday(" & "'Me.DTPicker9.Value'" & ") IN" & _
"( SELECT day_of_the_week " & _
"FROM Coach_Availability " & _
"WHERE Start_time = " & "'Me.Combostart.Value'" & ");"

Can anyone help me otu here?
Thanks !!

[MMcCarthy]

 
Me.comboCoach.RowSource = "Select c.Coach_Id, c.Coach_first_name, c.Coach_last_name " & _
"FROM Coach c INNER JOIN Coach_rates cr ON c.Coach_ID = cr.Coach_ID " & _
"WHERE cr.Sport_ID = " & Me.comboSport.Value & _
'<Comment: You need a space before the AND and put single quotes into string only not around value.   You don't need the .Value.  Space after IN.>
" AND weekday(" & Me.DTPicker9 & ") IN" & _
"(SELECT day_of_the_week " & _ 
"FROM Coach_Availability " & _
'<Comment: Put single quotes into string and surround value not around value.   You don't need the .Value.>
"WHERE Start_time = '" & Me.Combostart & "');"
 

I hope you can follow the comments

Any questions please ask

Mary

[NeoPa]

I had my reply all prepared and ready a while ago then I took a call and found this when I got back :(.
Mary's version has more than I found anyway. It assumes [Start_Time] is a string though. You'll need to change that if it's not.
Let us know if you have more problems.

[MMcCarthy]

I had my reply all prepared and ready a while ago then I took a call and found this when I got back :(.
Mary's version has more than I found anyway. It assumes [Start_Time] is a string though. You'll need to change that if it's not.
Let us know if you have more problems.

Adrian is of course right, as always. :)

If start_time is of a Date/Time data type. Replace the single quotes with #.

Mary

[robtyketto]

Thanks alot didnt quite follow all the comments :(
Still im learning more VBA code all the time!


Me.comboCoach.RowSource = "Select c.Coach_Id, c.Coach_first_name, c.Coach_last_name " & _
"FROM Coach c INNER JOIN Coach_rates cr ON c.Coach_ID = cr.Coach_ID " & _
"WHERE cr.Sport_ID = " & Me.comboSport & _
" AND weekday(" & #Me.DTPicker9.Value # & ") IN" & _
"( SELECT day_of_the_week " & _
"FROM Coach_Availability " & _
"WHERE Start_time =" & #Me.Combostart# & ");"

[MMcCarthy]

Not quite ... try this:

Me.comboCoach.RowSource = "Select c.Coach_Id, c.Coach_first_name, c.Coach_last_name " & _
"FROM Coach c INNER JOIN Coach_rates cr ON c.Coach_ID = cr.Coach_ID " & _
"WHERE cr.Sport_ID = " & Me.comboSport & _
" AND weekday(#" & Me.DTPicker9 & "#) IN " & _
"(SELECT day_of_the_week " & _
"FROM Coach_Availability " & _
"WHERE Start_time =#" & Me.Combostart & "#);"

[robtyketto]

Thanks for all your help, VBA code working.
I now understand the syntax :)

I have one LAST field to populate with VBA code, its a Total fee field.

Total fee = Sports rate (combosport must be populated) + coach_rate (option if combocoach populated
then if client is a member they get 25% off.

How can I do this in VBA??
The problem being I want to store results of SQl in variables and then used them to populate text field
and have no experience of doing this before.

Also when do I populate the field as user may not select a coach.
Can attach piccy of form if neccessary which shows field orders.

Here's my inital thoughts (I dont want to change my current database schema):-



Sports rates
~~~~~~~~~~~~
Area_id
Facility_id
sport_id
Sports_rate


Coach_rates
~~~~~~~~~~~
coach_id
sport_id
coach_price

SQL queries
~~~~~~~~~~~

Sports_price =
(perhaps does this after sports combo box populated ??)
Select sports_price,
from sports_rates sr
where me.comboSport.value = sr.sport_ID
and me.comboArea.value = sr.facility
and me.combofacility.value = sr.facility_ID

Coach_price =
(perhaps does this after sports combo box populated ??)
Select cr.coach_price
from coach_rates cr
Where me.comboSport.value = sr.sport_ID
and me.combocoach.value = cr.coach_ID

Where to put this code??

select member = membership_ID
from Client c
where me.comboClient.value = c.client_ID'


If Membership_ID is not null
then
fee = (sports_rice + coach_price)/
else
fee = Sports_price + coach_price
endif;

[robtyketto]

[MMcCarthy]

You will need a command button as otherwise you would have to repeat this code on an event on at least 2 if not 3 controls.

 
Private Sub cmdTotalPrice_Click()
Dim sportsPrice As Currancy
Dim coachPrice As Currancy
Dim member As Long
 
   sportsPrice = nz(DLookup("[sports_price]","sports_rates","[sport_ID]=" & Me.comboSport & _
   " And [facility]=" & Me.comboArea & " And [facility_ID]=" & Me.combofacility), 0)
 
   coachPrice = nz(DLookup("[coach_price]","coach_rates","[sport_ID]=" & Me.comboSport & _
   " And [coach_ID]=" & Me.combocoach), 0)
 
   member = nz(DLookup("[membership_ID]","Client","[client_ID]=" & Me.comboClient), 0)
 
   If member <> 0 Then
      If coachPrice <> 0 Then
         Me.total_fee = ((sportsPrice + coachPrice) / 100) * 75
      Else
         Me.total_fee = (sportsPrice / 100) * 75
      End If
   Else
      If coachPrice <> 0 Then
         Me.total_fee = sportsPrice + coachPrice
      Else
         Me.total_fee = sportsPrice
      End If
   End If
 
End Sub
 

[NeoPa]

You will need a command button as otherwise you would have to repeat this code on an event on at least 2 if not 3 controls.

Alternatively, and assuming that it would actually work better from the multiple events, you could put the code in a separate procedure (can even be private in the module of the form) and just call the procedure from all the relevant event procedures. I normally work from the perspective that relying on a human operator to do things the right way is asking for trouble ;).

[robtyketto]

Thanks, thats a greast solution.

Im thinking of using the code in events 'after update' on combo box for sport and then again for coach.

What was the third event you were thinking of?

Thanks again, really appreciate the help, learnt alot in the last few days.
My assignment is due in on Friday and soon to be fully functional :)

[NeoPa]

Thanks, thats a greast solution.

Im thinking of using the code in events 'after update' on combo box for sport and then again for coach.

What was the third event you were thinking of?

Thanks again, really appreciate the help, learnt alot in the last few days.
My assignment is due in on Friday and soon to be fully functional :)

I suspect Mary was just speaking generally and didn't want to go and work it out exactly. I'm sure if there are only two then you're fine.
Good luck with your assignment.

[MMcCarthy]

I suspect Mary was just speaking generally and didn't want to go and work it out exactly. I'm sure if there are only two then you're fine.
Good luck with your assignment.

Just watch out for client (member) will have to be passed as a parameter to any funciton.

[robtyketto]

Thank for the help, it fell over though at the line below:-

Runtime error '2001' you cancelled the previous operation" placen in afterupdate in comboSport.

sportsPrice = Nz(DLookup("[sport_price]", "sport_rates", "[sport_ID]=" & Me.comboSport & _
" And [facility]=" & Me.comboArea & " And [facility_ID]=" & Me.combofacility), 0)

I didnt list you the field names so I made these small changes:-
Changed the the field [sports_price] to [sport_price] and table "Sports_rates" to "Sport_rates"

[NeoPa]

Thank for the help, it fell over though at the line below:-

Runtime error '2001' you cancelled the previous operation" placen in afterupdate in comboSport.

If it gave that error message then it didn't so much fail, as be cancelled by the operator (you).
I guess you hit Ctrl-Break on the keyboard.
Did it stop responding for a long time?

[robtyketto]

Retried again (Shutdown access and restarted) definately didnt press a key and the same message appeared.

This time Ive copied all the code from the event.

Private Sub comboSport_AfterUpdate()

Dim sportsPrice As Currency
Dim coachPrice As Currency
Dim member As Long

Me.comboCoach.RowSource = "Select c.Coach_Id, c.Coach_first_name, c.Coach_last_name " & _
"FROM Coach c INNER JOIN Coach_rates cr ON c.Coach_ID = cr.Coach_ID " & _
"WHERE cr.Sport_ID = " & Me.comboSport & _
" AND weekday(#" & Me.DTPicker9 & "#) IN " & _
"(SELECT day_of_the_week " & _
"FROM Coach_Availability " & _
"WHERE Start_time =#" & Me.Combostart & "#);"


sportsPrice = Nz(DLookup("[sport_price]", "sport_rates", "[sport_ID]=" & Me.comboSport & _
" And [facility]=" & Me.comboArea & " And [facility_ID]=" & Me.combofacility), 0)

coachPrice = Nz(DLookup("[coach_price]", "coach_rates", "[sport_ID]=" & Me.comboSport & _
" And [coach_ID]=" & Me.comboCoach), 0)

member = Nz(DLookup("[membership_ID]", "Client", "[client_ID]=" & Me.ComboClient), 0)

If member <> 0 Then
If coachPrice <> 0 Then
Me.Total_fee = ((sportsPrice + coachPrice) / 100) * 75
Else
Me.Total_fee = (sportsPrice / 100) * 75
End If
Else
If coachPrice <> 0 Then
Me.Total_fee = sportsPrice + coachPrice
Else
Me.Total_fee = sportsPrice
End If
End If

End Sub

[MMcCarthy]

There is nothing in this code to cancel an operation. Are you sure there is no other code running on this control? Check the events list in the control properties.

Retried again (Shutdown access and restarted) definately didnt press a key and the same message appeared.

This time Ive copied all the code from the event.

Private Sub comboSport_AfterUpdate()

Dim sportsPrice As Currency
Dim coachPrice As Currency
Dim member As Long

Me.comboCoach.RowSource = "Select c.Coach_Id, c.Coach_first_name, c.Coach_last_name " & _
"FROM Coach c INNER JOIN Coach_rates cr ON c.Coach_ID = cr.Coach_ID " & _
"WHERE cr.Sport_ID = " & Me.comboSport & _
" AND weekday(#" & Me.DTPicker9 & "#) IN " & _
"(SELECT day_of_the_week " & _
"FROM Coach_Availability " & _
"WHERE Start_time =#" & Me.Combostart & "#);"


sportsPrice = Nz(DLookup("[sport_price]", "sport_rates", "[sport_ID]=" & Me.comboSport & _
" And [facility]=" & Me.comboArea & " And [facility_ID]=" & Me.combofacility), 0)

coachPrice = Nz(DLookup("[coach_price]", "coach_rates", "[sport_ID]=" & Me.comboSport & _
" And [coach_ID]=" & Me.comboCoach), 0)

member = Nz(DLookup("[membership_ID]", "Client", "[client_ID]=" & Me.ComboClient), 0)

If member <> 0 Then
If coachPrice <> 0 Then
Me.Total_fee = ((sportsPrice + coachPrice) / 100) * 75
Else
Me.Total_fee = (sportsPrice / 100) * 75
End If
Else
If coachPrice <> 0 Then
Me.Total_fee = sportsPrice + coachPrice
Else
Me.Total_fee = sportsPrice
End If
End If

End Sub

[NeoPa]

I have seen Access spontaneously cancel itself before, but never with an error message. It just stops quietly so you don't even know until you check :(.