Search results

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") & "#) " & _...

Sorry to be a bit slow but when you say box do you mean a physical archive box? Didn't completely understand your post. Would add though that everything to do with the contract should be in the Contract table. You seem to be implying that the contract details should reside with info about the...

Another thought... Have you considered reinstalling Access. There may be some problem with your installation! 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...

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...

Do your Access field names and Excel column names match exactly? I think they must for the Get External Data function to work. Another possible solution (if matching the names isn't an option) is to import to a new (temp) table and then run an append query. hth shay :cool:

What error message are you getting? I don't see why having an autonumber field should be a problem - you just don't append anything to that field and it inserts a unique number for each new record. shay

Try this... Text3 = IIf(DCount("*", "PartNumbers tbl", Left([PartNo], 2) = "84") > 0, "EXPORT", "DOMESTIC") (My example table is called "PartNumbers tbl" and part numbers stored in field "PartNo".) hth shay :cool:

Hi You forgot to assign your database ie Set dbs = OpenDatabase ... Other than that I don't see anything else wrong. I wouldn't expect the IF statement to be executed if no recordsets had been set. shay :cool:

Steve I'm sure the calculation could be done by a formula but it would be complicated because you need to select a price for each day of the stay depending on the hotel and room type. I chose to use code as I thought it was easier to use a Do Loop to build up the total price. Anyway, I hope...

Morning Steve I'm afraid I forgot to try to code on my PC at home. I've had another look and can't work out why you get error messages when it works perfectly for me. I suggest you create another thread, include the code and ask for comments. This thread's been going on for so long that I...

Hayvek Difficult to say where you should put the code as I don't know what you're trying to do. Hopefully this example will help. I created a form which has table "Random tbl" as recordsource. The table contains integer field "RandomNo" which is the controlsource for text box "txtRandomNo"...

Steve Think I may know what the problem is. In the last bit of the SQL statement I am comparing ThisDate (which is a string) with SeasonStartDate (a Date). I've got a different version of Access at home so I'll try it there and get back to you on Monday. Have a good weekend shay :cool:

Steve The total won't be correct because we've just removed the bit of code which tells the function which seasonal cost to choose. What we need to do now is try to work out why we get an error with the date bit of MySQL. Open table HotelPrices tbl in design mode. Click on the SeasonStartDate...

That looks ok to me. The only difference between that and what I get here is that on my pc ThisDate is displayed as #09/23/03#. Let's try to work out if the problem really is with the date. Change MySQL to MySQL = "([RoomType] = '" & Me.cmbRoomType & "') and " & _ "([HotelID] = " &...

Hi Steve I need to know the VALUE of MySQL. Put in the line MsgBox MySQL after MySQL = "([RoomType = ... and let me know what you get. The end must be in sight ..... shay :cool:

OK! Can you give me the content of MySQL - one example will do.

Steve After the TotalCost = TotalCost + DaysPrice(ThisDate) line it should jump to Function DaysPrice. Is it working out a sensible value for DaysPrice? (Again hold cursor over the variable name or use a Msgbox command.) shay :cool:

Steve We must be nearly there! Let's try again. You need to put a breakpoint in the code so that execution halts BEFORE the error occurs. So, click in the left-hand margin on the line ThisDate = "#" ... Go back to the form. Enter your data and click the button. The code window should open...

Steve If you step through the code using the F8 key, you will see if it ever gets to the line TotalCost = TotalCost + DaysPrice(ThisDate). If it never gets there, then the code isn't calling Function DaysPrice and this would explain why me.txtTotalCost is null. Shay