Can anyone see the problem with this code?

[Elephant]

Hi again

Yes, sorry, I had just RoomTyoe in there, though had cmbHOtelName etc for the others ...

Now, I out the dates in again and got an error message that we got before, so I KNOW we are near the finish line!

Error was that there was a "Missing Operator in Query Expression"

Code I have is :

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 & ")")


Can you see something wrong there - ?

 

[shay]

Hi

I cut and pasted the code into my db and it works. Check the names of your controls, tables and fields. If they are all correct try to work out where your error is occurring

Try

Set rst = CurrentDb.OpenRecordset("SELECT [HotelPrices tbl].[Price] FROM [HotelPrices tbl] " & _
"WHERE (([HotelPrices tbl].[HotelID]) = " & Me.[cmbHotelName] & ") " )

If that works try

Set rst = CurrentDb.OpenRecordset("SELECT [HotelPrices tbl].[Price] FROM [HotelPrices tbl] " & _
"WHERE (([HotelPrices tbl].[HotelID]) = " & Me.[cmbHotelName] & ") " & _
"And (([HotelPrices tbl].[RoomType]) = '" & Me.[cmbRoomType] & "') " )

and so on. This will pinpoint the offending line.

shay

 

[Elephant]

Hi again,

I cut some of the code text as you suggested - now giving below - it made the same error - I checked all the names etc and they seem all OK ... :

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] & ") ")


TotalCost = TotalCost + rst![Price]
Next i

Me.txtTotalCost = TotalCost
End Sub

 

[Tay]

I think the problem may be with this line

"WHERE (([HotelPrices tbl].[HotelID]) = " & Me.[cmbHotelName] & ") ")

You have 2 of these brackets (, but 3 of these brackets ).


This won't help with your problem, but you need to un-set the recordset.
Type this

set rst = nothing

just before end sub.

HTH

 

[Elephant]

I wnet through all the field names etc and triple-checked all items same as the text form that did work - there were a few minor differences which I corrected - mainly that I was putting a value in "Contol Source" where you did not. I then put back the whole code and got a NEW error message !

Run-time error '13'
Type mismatch


??

 

[Tay]

I just look at the contents and index bit in Access under Type Mismatch. Could this be your problem?

Verify that the criteria you specified is for the same data type as the data in the underlying table or query. For example, the field ReorderLevel has a Number data type. Therefore, if you type the criteria "50", you'll get an error because Microsoft Access interprets values in quotation marks as text, not numbers.

I am not good when it comes to code, but could it be this line where you have "d" in quotation marks that's causing the error message? Is "d" a text field or number field?

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

and with this line

ThisDate = "#" & Format(DateAdd("d", i, Me.txtFromDate), "mm/dd/yy") & "#"

I really haven't looked in much details at your code, so I am probably completely off course.

 

[Elephant]

Hello Tay,

Thanks for that. I didn't write the code, so I am not sure ... I don't think there should be any data differences, though with regards the code, I'm not even sure how to check.

Hopefully Shay knows ...

 

[shay]

Steve

I know that you said you've checked everything but something's still not quite right. Check the field data type in every table against the example that works.

You are probably entering text into a numeric field or perhaps you haven't dimensioned your variables correctly in the code.

Sorry but check again.

shay

 

[Elephant]

Hi Shay,

I checked again, and the only thing I could find that was different was that your form source was "Hotels Qry" and mine was something different, a table called "Booking Details". M calculation is now in a sub-form, not a form.

Do you think either of those 2 factors could be causing the errors?

 

[shay]

Hi

The name of the data source doesn't matter - what DOES matter is that the fields in both match EXACTLY - name and data type. Can you strip out your data and post the file? I'll send you my e-mail address. It'll probably be quicker if I can look at your file.

shay

 

[Elephant]

Will do .. thanks a lot ...