Help Using If/Else to Choose a Query (1 Viewer)

[hedonismbot]

Hey there everyone,

I'm an Access beginner and am currently working on a reservation system for desks. I have run into an issue with a set of If/Else statements I've implemented to try to achieve what the user requested.

If a user makes a reservation that's two days or less out from the current date- they receive a certain set of desks (qryAvailableDesks). If the user selects a reservation date that's more than two days out, they receive a separate set of desks (qryAvailableDesks2).

I've attached the following code to a button on a form to achieve this:

Private Sub Command108_Click()
If Me.Reservation_Date = [ReservationStartDate] <= Date + 2 Then
Me.listDeskSearch.RowSource = "qryAvailableDesks"
ElseIf Me.Reservation_Date = [ReservationStartDate] > Date + 2 Then
Me.listDeskSearch.RowSource = "qryAvailableDesks2"
End If
End Sub

But, for some reason, the RowSource change isn't happening and I'm thinking it has to do with the way I've written the date calculation?

I've been stumped by this for a couple of days and it's probably because I'm thinking about this incorrectly. Any assistance would be greatly appreciated.

 

[bob fitz]

Perhaps:

Private Sub Command108_Click()
If Me.Reservation_Date <= DateAdd("d",2,[ReservationStartDate] Then
  Me.listDeskSearch.RowSource = "qryAvailableDesks"
ElseIf Me.Reservation_Date > DateAdd("d",2,[ReservationStartDate]) Then
  Me.listDeskSearch.RowSource = "qryAvailableDesks2"
End If
End Sub

 

[hedonismbot]

Perhaps:

Private Sub Command108_Click()
If Me.Reservation_Date <= DateAdd("d",2,[ReservationStartDate] Then
  Me.listDeskSearch.RowSource = "qryAvailableDesks"
ElseIf Me.Reservation_Date > DateAdd("d",2,[ReservationStartDate]) Then
  Me.listDeskSearch.RowSource = "qryAvailableDesks2"
End If
End Sub

Thank you so much for your help and I apologize for not getting back to you sooner. Life got crazy for a second. I'll give this a try and, hopefully, be able to get it to work.

 

[The_Doc_Man]

Bob gave you a viable alternative expression. I'll explain your error so you will know how to avoid it later.

If Me.Reservation_Date = [ReservationStartDate] <= Date + 2 Then

If you look at this expression between the IF and the THEN, you have four values and three operators. This expression is legal but will be interpreted as follows:

Me.Reservation_Date = [ReservationStartDate] is a sub-expression of testing for equality between the two dates. (Perfectly legal, both in syntax and semantics.) Call the intermediate result X. The next part of the expression is effectively X <= Date + 2. By operator precedence rules, Date + 2 will be evaluated before the whole expression. Call that part Y. What is left is X <= Y. Where things get critical, though, is if we look at what is the result of those expressions. The sub-expression I called X is an equals-sign expression - a comparison - which by language rules will return either TRUE or FALSE. That is, either the dates are equal or they are not. The sub-expression I called Y is a date arithmetic expression, which is legal. You would get the result of today's date plus 2 days if you executed that. Perfectly legal, perfectly permissible. Given your concern regarding "advance booking" beyond 2 days, not at all unreasonable.

What you end up with when you have to deal with the remaining sub-expression is that the <= operator is comparing a TRUE/FALSE value to something that is not a TRUE/FALSE value. (It is still a date.) So your observation that the expression doesn't seem to work, probably due to your date calculation, is spot-on accurate.

Oddly enough, if you had executed this expression outside of the context of an IF statement, you might have gotten a data type mismatch or might not have, because none of the elements of the expression are inherently illegal. Your result just wouldn't be the data type you anticipated. In your separate intro, you suggested that you were not as clear on VBA as on other things. Which is why I'm taking the time to explain your error so you might understand a little better how VBA works.

 

[bob fitz]

Bob gave you a viable alternative expression. I'll explain your error so you will know how to avoid it later.

If Me.Reservation_Date = [ReservationStartDate] <= Date + 2 Then

If you look at this expression between the IF and the THEN, you have four values and three operators. This expression is legal but will be interpreted as follows:

Me.Reservation_Date = [ReservationStartDate] is a sub-expression of testing for equality between the two dates. (Perfectly legal, both in syntax and semantics.) Call the intermediate result X. The next part of the expression is effectively X <= Date + 2. By operator precedence rules, Date + 2 will be evaluated before the whole expression. Call that part Y. What is left is X <= Y. Where things get critical, though, is if we look at what is the result of those expressions. The sub-expression I called X is an equals-sign expression - a comparison - which by language rules will return either TRUE or FALSE. That is, either the dates are equal or they are not. The sub-expression I called Y is a date arithmetic expression, which is legal. You would get the result of today's date plus 2 days if you executed that. Perfectly legal, perfectly permissible. Given your concern regarding "advance booking" beyond 2 days, not at all unreasonable.

What you end up with when you have to deal with the remaining sub-expression is that the <= operator is comparing a TRUE/FALSE value to something that is not a TRUE/FALSE value. (It is still a date.) So your observation that the expression doesn't seem to work, probably due to your date calculation, is spot-on accurate.

Oddly enough, if you had executed this expression outside of the context of an IF statement, you might have gotten a data type mismatch or might not have, because none of the elements of the expression are inherently illegal. Your result just wouldn't be the data type you anticipated. In your separate intro, you suggested that you were not as clear on VBA as on other things. Which is why I'm taking the time to explain your error so you might understand a little better how VBA works.

Thanks The Doc Man

You more experienced, younger guys are so much better at the technical explanations than I am

 

[The_Doc_Man]

Younger? Only at heart, perhaps... but thanks for the kind words, Bob!