PhilipEwen
Registered User.
- Local time
- Today, 16:57
- Joined
- Jun 11, 2001
- Messages
- 81
Hi,
I am trying to insert a set of data which is entered from an unbound form into a table.
I want to enter a From: date and a To: date and the by calculating the difference in days between the 2 dates, loop through an insert statement to enter the same data, but just adding 1 day to the date each time.
I may have gone arse over tit on this, but it seems the way to do it !! :0)
I have 2 problems...
1. the insert command is fine if i use the Forms!hoteldetails!hotelsresortid as the data, but i need to be able to add a day to that date - therefore i have assigned a variable 'entrydate' and would normally just add a day.
Unfortunately i keep getting an 'Enter parameter Value' for entrydate if i put this in the SQL statement
Surely this is just a syntax thing...but i have tried all sorts of methods and am still stuck.
PROBLEM 2:
I have calculated the difference in days between the dates using DateDiff - no problem.
I then start my loop, insert the data, add a day ( if i can get prob.1 sorted ! ), add 1 to the count for the loop and loop again until all days are filled.
The problem is that it seems to add 2 to the loop count variable each time, therefore if i have a date diff of 6 days, it'll only loop 3 times, not 6 !!!!!!!!!!!
Could someone PLEASE PLEASE PLEASE check my code and tell me why i am being an idiot !
Many thanks
Private Sub btn_enter_new_prices_Click()
' check if text boxes are filled If [txt_from] <> "" And [txt_to] <> "" And [txt_hb] <> "" And [txt_al] <> "" And [txt_sc] <> "" And [txt_bb] <> "" Then
Dim ss As String
Dim dateinterval As Integer
Dim firstdate As Date
Dim lastdate As Date
Dim counter As Integer
Dim entrydate As Date
'set up variables
entrydate = Forms!hoteldetails!txt_from 'set the entry date to enter the correct data to the From field
counter = 1 ' counts the loops
firstdate = Forms!hoteldetails!txt_from 'firstdate for calculation
lastdate = Forms!hoteldetails!txt_to ' last date for calculation
dateinterval = DateDiff("d", [firstdate], [lastdate]) 'sets the number of days between the dates
dateinterval = dateinterval + 1 ' add 1 to include last day
'start loop
For counter = 1 To dateinterval
ss = "" 'clears any old data
ss = "INSERT INTO [hotels] ( hotelid,hotelname,hotelsresortid,[date],hb,al,sc,bb ) VALUES (Forms!hoteldetails!hotelid,Forms!hoteldetails!hotelname,Forms!hoteldetails!hotelsresortid,[entrydate],Forms!hoteldetails!txt_hb,Forms!hoteldetails!txt_al,Forms!hoteldetails!txt_sc ,Forms!hoteldetails!txt_bb);"
'DoCmd.SetWarnings (False)
DoCmd.RunSQL ss
'DoCmd.SetWarnings (True)
Me.Requery
entrydate = entrydate + 1
If counter > dateinterval Then Exit For
counter = counter + 1
Next counter
End Sub
I am trying to insert a set of data which is entered from an unbound form into a table.
I want to enter a From: date and a To: date and the by calculating the difference in days between the 2 dates, loop through an insert statement to enter the same data, but just adding 1 day to the date each time.
I may have gone arse over tit on this, but it seems the way to do it !! :0)
I have 2 problems...
1. the insert command is fine if i use the Forms!hoteldetails!hotelsresortid as the data, but i need to be able to add a day to that date - therefore i have assigned a variable 'entrydate' and would normally just add a day.
Unfortunately i keep getting an 'Enter parameter Value' for entrydate if i put this in the SQL statement
Surely this is just a syntax thing...but i have tried all sorts of methods and am still stuck.
PROBLEM 2:
I have calculated the difference in days between the dates using DateDiff - no problem.
I then start my loop, insert the data, add a day ( if i can get prob.1 sorted ! ), add 1 to the count for the loop and loop again until all days are filled.
The problem is that it seems to add 2 to the loop count variable each time, therefore if i have a date diff of 6 days, it'll only loop 3 times, not 6 !!!!!!!!!!!
Could someone PLEASE PLEASE PLEASE check my code and tell me why i am being an idiot !
Many thanks
Private Sub btn_enter_new_prices_Click()
' check if text boxes are filled If [txt_from] <> "" And [txt_to] <> "" And [txt_hb] <> "" And [txt_al] <> "" And [txt_sc] <> "" And [txt_bb] <> "" Then
Dim ss As String
Dim dateinterval As Integer
Dim firstdate As Date
Dim lastdate As Date
Dim counter As Integer
Dim entrydate As Date
'set up variables
entrydate = Forms!hoteldetails!txt_from 'set the entry date to enter the correct data to the From field
counter = 1 ' counts the loops
firstdate = Forms!hoteldetails!txt_from 'firstdate for calculation
lastdate = Forms!hoteldetails!txt_to ' last date for calculation
dateinterval = DateDiff("d", [firstdate], [lastdate]) 'sets the number of days between the dates
dateinterval = dateinterval + 1 ' add 1 to include last day
'start loop
For counter = 1 To dateinterval
ss = "" 'clears any old data
ss = "INSERT INTO [hotels] ( hotelid,hotelname,hotelsresortid,[date],hb,al,sc,bb ) VALUES (Forms!hoteldetails!hotelid,Forms!hoteldetails!hotelname,Forms!hoteldetails!hotelsresortid,[entrydate],Forms!hoteldetails!txt_hb,Forms!hoteldetails!txt_al,Forms!hoteldetails!txt_sc ,Forms!hoteldetails!txt_bb);"
'DoCmd.SetWarnings (False)
DoCmd.RunSQL ss
'DoCmd.SetWarnings (True)
Me.Requery
entrydate = entrydate + 1
If counter > dateinterval Then Exit For
counter = counter + 1
Next counter
End Sub
