Can anyone see the problem with this code?

[Elephant]

I am developing a database for a travel company.

Attached is a small test part of that database where I am trying to input dates of hotel stay in a form and have price of stay automatically calculated.

For some reason the code used in the Form "Hotels Frm" does not work and I get a syntax error. Could someone kindly check it and let me know why there is a problem (and what to change) as I just can't figure it out ... you need to go to the form "Hotels frm", choose a hotel, room, and then arrival and departure date ...

The code in question is :
Set rst = CurrentDb.OpenRecordset("SELECT [HotelPrices tbl].[Price] " _
& "FROM [HotelPrices tbl] " _
& "INNER JOIN [Hotels tbl] ON " _
& "[HotelPrices tbl].HotelId = [Hotels tbl].HotelID " _
& "WHERE ((([Hotels tbl].[HotelName]) = '" & Me.[cmbHotelName] & "') " _
& "And (([HotelPrices tbl].[SeasonStartDate]) < #" & Me.[txtFromDate] & "#) " _
& "And (([HotelPrices tbl].[SeasonEndDate]) > #" & Me.[txtFromDate] & "#)) " _
& "GROUP BY [HotelPrices tbl].[Price];")
Price = rst![Price]

The error message I got was :
Run-time error '3075'

Syntax error in date query expression

'((([Hotels tbl].[HotelName]) = 'Sunshine Villa') And (([HotelPrices tbl].[SeasonStartDate]) < #23.3.2003#) And (([HotelPrices tbl].[SeasonEndDate]) > #23.3.2003#))'.


THANKS!

Steve

 

[shay]

Hi there

Sorry to see that you are still struggling with this one!

Answer to your questions is

'((([Hotels tbl].[HotelName]) = 'Sunshine Villa') And (([HotelPrices tbl].[SeasonStartDate]) < #23.3.2003#) And (([HotelPrices tbl].[SeasonEndDate]) > #23.3.2003#))'

Presumably you do not know any other Access users in Finland who you could talk to - I'm convinced the reason we couldn't get that example db working on your pc is to do with date specifications. Couldn't look at ps.mdb because I'm running Access 97!

Keep smiling

shay

 

[Elephant]

Hi Shay,

Thanks for the red marks - actually it was just my typo in the original code as I had to type it off the error message box (it would not copy and paste) and missed those 2 punctuation marks ... they are in fact there on the original.

The actual code where there erro appears is (and this is actually copied and pasted from the file) :

Set rst = CurrentDb.OpenRecordset("SELECT [HotelPrices tbl].[Price] " _
& "FROM [HotelPrices tbl] " _
& "INNER JOIN [Hotels tbl] ON " _
& "[HotelPrices tbl].HotelId = [Hotels tbl].HotelID " _
& "WHERE ((([Hotels tbl].[HotelName]) = '" & Me.[cmbHotelName] & "') " _
& "And (([HotelPrices tbl].[SeasonStartDate]) < #" & Me.[txtFromDate] & "#) " _
& "And (([HotelPrices tbl].[SeasonEndDate]) > #" & Me.[txtFromDate] & "#)) " _
& "GROUP BY [HotelPrices tbl].[Price];")
Price = rst![Price]


Sadly I have no Access contact in Finland ---- but the code above was written by someone in US - he says it worked on his machine, but not (naturally!) on mine. It again appears to be something to do with dates ................ ahhh!!!

Or can you see something else from the code above - you will recognise your fields ;-)

Steve

 

[shay]

Hi again

A couple of points.

Many, many posts ago I mentioned the fact that we always have to reformat dates to the US format when using date functions. So change both occurrences of

#" & Me.[txtFromDate] & "#) " _

to

#" & format(Me.[txtFromDate], "mm/dd/yy") & "#) " _

Not sure how much the example file I sent you has changed but you need to check whether cmbHotelName is bound to the hotel's ID or name. If cmbHotelName is actually returning the HotelID then you need to change

"WHERE ((([Hotels tbl].[HotelName]) = '" & Me.[cmbHotelName] & "') " _

to

"WHERE ((([Hotels tbl].[HotelID]) = '" & Me.[cmbHotelName] & "') " _

See if that works. You will still need to modify the SQL statement because it doesn't include anything about room type. Also including table Hotels tbl in the SQL is probably not necessary but let's take one step at a time!

shay

 

[shay]

Another thought...

Have you considered reinstalling Access. There may be some problem with your installation!

shay

 

[Elephant]

Hello Shay,

I changed the date format as you suggested :

Set rst = CurrentDb.OpenRecordset("SELECT [HotelPrices tbl].[Price] " _
& "FROM [HotelPrices tbl] " _
& "INNER JOIN [Hotels tbl] ON " _
& "[HotelPrices tbl].HotelId = [Hotels tbl].HotelID " _
& "WHERE ((([Hotels tbl].[HotelName]) = '" & Me.[cmbHotelName] & "') " _
& "And (([HotelPrices tbl].[SeasonStartDate]) < #" & Format(Me.[txtFromDate], "mm/dd/yy") & "#) " _
& "And (([HotelPrices tbl].[SeasonEndDate]) > #" & Format(Me.[txtFromDate], "mm/dd/yy") & "#) " _
& "GROUP BY [HotelPrices tbl].[Price];")

Now, I got the error "Syntax error (missing operator) in query expression"

Re the cmbHotelName, I believe the query concerned is this :

SELECT [HotelPrices tbl].Price
FROM [HotelPrices tbl] INNER JOIN [Hotels tbl] ON [HotelPrices tbl].HotelId = [Hotels tbl].HotelID
WHERE ((([Hotels tbl].HotelName)=[Forms]![Hotels frm]![cmbHotelName]) AND (([HotelPrices tbl].SeasonStartDate)<[Forms]![Hotels frm]![txtFromDate]) AND (([HotelPrices tbl].SeasonEndDate)>[Forms]![Hotels frm]![txtFromDate]))
GROUP BY [HotelPrices tbl].Price;


I have Access installed on 4 seperate computers, and installed it newly on my lap top, all with the same error results!!

 

[shay]

Cut and paste ...

Set rst = CurrentDb.OpenRecordset("SELECT [HotelPrices tbl].[Price] " & _
"FROM [HotelPrices tbl] " & _
"WHERE ((([HotelPrices tbl].[HotelID]) = " & Me.[cmbHotelName] & ") " & _
"And (([HotelPrices tbl].[SeasonStartDate]) < #" & Format(Me.[txtFromDate], "mm/dd/yy") & "#) " & _
"And (([HotelPrices tbl].[SeasonEndDate]) > #" & Format(Me.[txtFromDate], "mm/dd/yy") & "#)); ")

This assumes that cmbHotelName gives the Hotel ID.

shay

 

[Elephant]

First, I should mention that the form you made DID work when this other person had amended the code to check the Day of the Week, such If day is Saturday Price = 50, if Sunday Price = 45 etc. Then it worked! So, it does actually function, just that when we work on the dates it doesn't !


I pasted in your code, but still got the error.

Whole code now looks like this :

Option Compare Database
Option Explicit

Private Sub CalculatePrice()
Dim YourDate
Dim Price As Long
Dim NumberOfDays As Long
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [HotelPrices tbl].[Price] " & _
"FROM [HotelPrices tbl] " & _
"WHERE ((([HotelPrices tbl].[HotelID]) = " & Me.[cmbHotelName] & ") " & _
"And (([HotelPrices tbl].[SeasonStartDate]) < #" & Format(Me.[txtFromDate], "mm/dd/yy") & "#) " & _
"And (([HotelPrices tbl].[SeasonEndDate]) > #" & Format(Me.[txtFromDate], "mm/dd/yy") & "#)); ")


Price = rst![Price]

'This portion is to determine the amount of days the party is staying
NumberOfDays = DateDiff("d", Me.txtFromDate.Value, Me.txtToDate.Value)
Me.txtTotalCost.Value = NumberOfDays * Price

End Sub

Private Sub cmbHotelName_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub cmdCalculate_Click()
Call CalculatePrice
End Sub

Private Sub txtFromDate_AfterUpdate()
If Not IsNull(Me.txtToDate) Or Me.txtToDate <> "" Then
Call CalculatePrice
End If
End Sub

Private Sub txtToDate_AfterUpdate()
If Not IsNull(Me.txtFromDate) Or Me.txtFromDate <> "" Then
Call CalculatePrice
End If
End Sub


Only change the other person made to your file was he added a union query (which I pasted above). Do you know why the code :

& "And (([HotelPrices tbl].[SeasonStartDate]) < #" & Me.[txtFromDate] & "#) " _
& "And (([HotelPrices tbl].[SeasonEndDate]) > #" & Me.[txtFromDate] & "#)) " _


... why it uses the < textFromdate and > textFromDate

Nothing about TextToDate ...?

 

[shay]

I cut and pasted all that code into the Db and it ran with no problems at all.

Do you know why the code :& "And (([HotelPrices tbl].[SeasonStartDate]) < #" & Me.[txtFromDate] & "#) " _
& "And (([HotelPrices tbl].[SeasonEndDate]) > #" & Me.[txtFromDate] & "#)) " _


... why it uses the < textFromdate and > textFromDate

Nothing about TextToDate ...?

Basically, even if you could get it to run, this code wouldn't solve your problem. What it does is select a price based on the 'From date', work out how many days the room is needed and multiplies the two numbers. It doesn't allow for different prices for different dates.

But we need not worry about that just yet. The first thing is to get round this date problem you have. I will try to think of another approach.

Can you just check the value of me.cmbHotelName. Put the line

msgbox me.cmbHotelName

above the Set rst = ... line.

Does it give you the hotel name or ID number?

shay

 

[Elephant]

Thanks Shay, for your time again ... its very weird it works on your machine and not mine!

Anyways ... the Hote Name comes up, not the Hotel ID ...

 

[shay]

Open the form in Design mode. Check the RowSource property for cmbHotelName. It should say

SELECT [Hotels tbl].HotelID, [Hotels tbl].HotelName from [Hotels tbl];

Check that Bound Column = 1, Column Count = 2 and Column Widths = 0cm; 4cm.

Run it again. cmbHotelName should give the ID code.

shay

 

[Elephant]

Hi Shay,

Yes, it now gives the Hotel ID .... AND : I get a price!!!

You are correct, it does not take into account the Room Type. but ... now the error has gone.

Are we getting there ?!

 

[shay]

Brilliant news! I will abandon my new approach and see if we can get your current version working correctly.

Watch this space!!!

shay

 

[Elephant]

I am not sure if this was the cure, but I went to my Windows control panel, and changed the default date to / instead of .

ie, 23/06/03 instead of 23.06.03

 

[shay]

Hi

Replace your current CalculatePrice with this sub.


Public Sub CalculatePrice()

Dim TotalCost As Integer, ThisDate As String, NoDays As Integer, i As Integer, rst As Recordset

MsgBox "Wants a " & Me.cmbRoomType & " room from " & Me.txtFromDate & " to " & Me.txtToDate

TotalCost = 0
NoDays = DateDiff("d", Me.txtFromDate, Me.txtToDate)

For i = 0 To NoDays - 1
ThisDate = "#" & Format(DateAdd("d", i, Me.txtFromDate), "mm/dd/yy") & "#"

Set rst = CurrentDb.OpenRecordset("SELECT [HotelPrices tbl].[Price] FROM [HotelPrices tbl] " & _
"WHERE (([HotelPrices tbl].[HotelID]) = " & Me.[cmbHotelName] & ") " & _
"And (([HotelPrices tbl].[RoomType]) = '" & Me.[cmbRoomType] & "') " & _
"And (([HotelPrices tbl].[SeasonStartDate]) <= " & ThisDate & _
") And (([HotelPrices tbl].[SeasonEndDate]) >= " & ThisDate & ")")
TotalCost = TotalCost + rst![Price]
Next i

Me.txtTotalCost = TotalCost
End Sub


Fingers crossed!!

shay

 

[Elephant]

Hi Shay,

It works !!!

I calculates correctly for different room types and also over the price range ....

THANK you sooooo much for that! You are a GENIUS!

Now ... just the small matter of taking the code etc over into my "real database" .... it will eventually appear as a sub-form to a form, and in datasheet view - will that be a problem? Is it going to be easy to incorporate it into my other database, or just a matter of re-naming the fields in the code as appropriate to the new tables etc ...

THANKS for all you time, really much appreciated!

 

[shay]

I'm so pleased we've hit on a solution. The sub CalculatePrice should transport to your db - just change the names of the text boxes and tables as appropriate.

Best of luck.

shay

 

[Elephant]

Thanks Shay,

Will work on this, and hopefully just let you know that is all worked fine!

 

[Elephant]

Hi Shay,

So nearly there with the price calculation thing - I transferred all info over to my main database and made the tables, changed the names etc.

I put in some text dates and got the following :

Compile Error : Method or Data Member not found.

Checking the code, it stopped at ".cmbRoomType" from the line MsgBox "Wants a " & Me.cmbRoomType & " room from " & Me.txtFromDate & " to " & Me.txtToDate

I guess this is a simple thing to cure ... ??

 

[shay]

Morning

Check the name of the room type combo box.

It should either be called cmbRoomType or change the name in the code to your new name.

shay