Hi all,
I am having a small issue with an update query - hopefully you will be able to help.
I am trying to update a table that has column headings as follows:
Gas Day|Gas Hour|Sitename|Avaliability
The raw data is received in such a form, so for each day I will have 24 rows of data. Each will be the same Gas Day, but with a different Gas Hour (running from 6 through to 30). Unfortunately there is no other way that I can get the raw data.
My problem comes with me needing to update the 'avaliability' data on a day by day basis. I currently have an update query generated off a form. Here the user can select a start date and start hour, an end date and end hour, a site, and a new level of avaliability.
If we are just changing one day, then the query works well. Lets take for example:
Start date/time: 1/1/11 Gas Hour 6
End date/time: 1/1/11 Gas Hour 12
The query will update the data as expected. However, problems arise when changing the data for more than one day.
Lets take a similar example:
Start date/time: 1/1/11 Gas Hour 6
End date/time: 2/1/11 Gas Hour 12
Here the query will only update the data for the hours 6-12 on both days, when actually, the data needs to be changed FROM 6 on the 1st, TO 12 on the second. Obviously I have my query set up so that it only looks at the times as specified.
Is there any way at all that I can modify my query so that it will look at the dates and times inclusively, rather than exclusively?
Currently my query crtierea for Gas Day selection is:
Between [forms]![frmoutages].[txtdate1] And [forms]![frmoutages].[txtdate2]
and for Gas Hour:
Between [forms]![frmoutages].[cbogh1] And [forms]![frmoutages].[cbogh2]
I understand where I have gone wrong, but just cant think of a way to fix it. The only thing I have come up with so far is to have the user run the qery 3 seperate times. Once for the first day, once for the final day, and once for the intervening period. This is really an ugly way of doing things, so I don't want to go down this route if I can help it.
Many thanks in advance.
I am having a small issue with an update query - hopefully you will be able to help.
I am trying to update a table that has column headings as follows:
Gas Day|Gas Hour|Sitename|Avaliability
The raw data is received in such a form, so for each day I will have 24 rows of data. Each will be the same Gas Day, but with a different Gas Hour (running from 6 through to 30). Unfortunately there is no other way that I can get the raw data.
My problem comes with me needing to update the 'avaliability' data on a day by day basis. I currently have an update query generated off a form. Here the user can select a start date and start hour, an end date and end hour, a site, and a new level of avaliability.
If we are just changing one day, then the query works well. Lets take for example:
Start date/time: 1/1/11 Gas Hour 6
End date/time: 1/1/11 Gas Hour 12
The query will update the data as expected. However, problems arise when changing the data for more than one day.
Lets take a similar example:
Start date/time: 1/1/11 Gas Hour 6
End date/time: 2/1/11 Gas Hour 12
Here the query will only update the data for the hours 6-12 on both days, when actually, the data needs to be changed FROM 6 on the 1st, TO 12 on the second. Obviously I have my query set up so that it only looks at the times as specified.
Is there any way at all that I can modify my query so that it will look at the dates and times inclusively, rather than exclusively?
Currently my query crtierea for Gas Day selection is:
Between [forms]![frmoutages].[txtdate1] And [forms]![frmoutages].[txtdate2]
and for Gas Hour:
Between [forms]![frmoutages].[cbogh1] And [forms]![frmoutages].[cbogh2]
I understand where I have gone wrong, but just cant think of a way to fix it. The only thing I have come up with so far is to have the user run the qery 3 seperate times. Once for the first day, once for the final day, and once for the intervening period. This is really an ugly way of doing things, so I don't want to go down this route if I can help it.
Many thanks in advance.
