By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,472 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Help with this SQL statement into VBA

100+
P: 108
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.
Nov 24 '06 #1
Share this Question
Share on Google+
28 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
What is the logic of this?

Expand|Select|Wrap|Line Numbers
  1.  
  2. WHERE cr.sport_ID = [Forms]![FormName]![comboSport]
  3. and weekday([Forms]![FormName]![comboDate]) in
  4. (
  5. Select day 
  6. from coach_Availability 
  7. WHERE [time] = [Forms]![FormName]![comboStarttime]
  8. )
  9.  
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...

Expand|Select|Wrap|Line Numbers
  1. SELECT c.Coach_Id, c.coach_first_name, c.coach_second_name
  2. FROM Coach c INNER JOIN coachrates cr 
  3. ON c.coach_ID = cr.coach_ID
  4.  
Nov 25 '06 #2

NeoPa
Expert Mod 15k+
P: 31,186
What is the logic of this?

Expand|Select|Wrap|Line Numbers
  1. WHERE cr.sport_ID = [Forms]![FormName]![comboSport]
  2. and weekday([Forms]![FormName]![comboDate]) in
  3. (
  4. Select day 
  5. from coach_Availability 
  6. WHERE [time] = [Forms]![FormName]![comboStarttime]
  7. )
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 25 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Nov 25 '06 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 25 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 25 '06 #6

NeoPa
Expert Mod 15k+
P: 31,186
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)'.
Nov 25 '06 #7

NeoPa
Expert Mod 15k+
P: 31,186
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].
Nov 25 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 25 '06 #9

NeoPa
Expert Mod 15k+
P: 31,186
That's a good point.
Maybe you could post a confirmation of your cleverness robtyketto ;).
Nov 25 '06 #10

100+
P: 108
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 :)
Nov 25 '06 #11

100+
P: 108
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 !!
Nov 25 '06 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1.  
  2. Me.comboCoach.RowSource = "Select c.Coach_Id, c.Coach_first_name, c.Coach_last_name " & _
  3. "FROM Coach c INNER JOIN Coach_rates cr ON c.Coach_ID = cr.Coach_ID " & _
  4. "WHERE cr.Sport_ID = " & Me.comboSport.Value & _
  5. '<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.>
  6. " AND weekday(" & Me.DTPicker9 & ") IN" & _
  7. "(SELECT day_of_the_week " & _ 
  8. "FROM Coach_Availability " & _
  9. '<Comment: Put single quotes into string and surround value not around value.   You don't need the .Value.>
  10. "WHERE Start_time = '" & Me.Combostart & "');"
  11.  

I hope you can follow the comments

Any questions please ask

Mary
Nov 25 '06 #13

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 25 '06 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 25 '06 #15

100+
P: 108
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# & ");"
Nov 25 '06 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
Not quite ... try this:

Expand|Select|Wrap|Line Numbers
  1. Me.comboCoach.RowSource = "Select c.Coach_Id, c.Coach_first_name, c.Coach_last_name " & _
  2. "FROM Coach c INNER JOIN Coach_rates cr ON c.Coach_ID = cr.Coach_ID " & _
  3. "WHERE cr.Sport_ID = " & Me.comboSport & _
  4. " AND weekday(#" & Me.DTPicker9 & "#) IN " & _
  5. "(SELECT day_of_the_week " & _
  6. "FROM Coach_Availability " & _
  7. "WHERE Start_time =#" & Me.Combostart & "#);"
Nov 25 '06 #17

100+
P: 108
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;
Nov 25 '06 #18

100+
P: 108

Nov 25 '06 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdTotalPrice_Click()
  3. Dim sportsPrice As Currancy
  4. Dim coachPrice As Currancy
  5. Dim member As Long
  6.  
  7.    sportsPrice = nz(DLookup("[sports_price]","sports_rates","[sport_ID]=" & Me.comboSport & _
  8.    " And [facility]=" & Me.comboArea & " And [facility_ID]=" & Me.combofacility), 0)
  9.  
  10.    coachPrice = nz(DLookup("[coach_price]","coach_rates","[sport_ID]=" & Me.comboSport & _
  11.    " And [coach_ID]=" & Me.combocoach), 0)
  12.  
  13.    member = nz(DLookup("[membership_ID]","Client","[client_ID]=" & Me.comboClient), 0)
  14.  
  15.    If member <> 0 Then
  16.       If coachPrice <> 0 Then
  17.          Me.total_fee = ((sportsPrice + coachPrice) / 100) * 75
  18.       Else
  19.          Me.total_fee = (sportsPrice / 100) * 75
  20.       End If
  21.    Else
  22.       If coachPrice <> 0 Then
  23.          Me.total_fee = sportsPrice + coachPrice
  24.       Else
  25.          Me.total_fee = sportsPrice
  26.       End If
  27.    End If
  28.  
  29. End Sub
  30.  
Nov 26 '06 #20

NeoPa
Expert Mod 15k+
P: 31,186
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 ;).
Nov 26 '06 #21

100+
P: 108
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 :)
Nov 26 '06 #22

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 26 '06 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 26 '06 #24

100+
P: 108
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"
Nov 26 '06 #25

NeoPa
Expert Mod 15k+
P: 31,186
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?
Nov 26 '06 #26

100+
P: 108
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
Nov 26 '06 #27

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 27 '06 #28

NeoPa
Expert Mod 15k+
P: 31,186
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 :(.
Nov 27 '06 #29

Post your reply

Sign in to post your reply or Sign up for a free account.