Update Query Issue

[Jameo]

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.

 

[SydneyGeek]

By having Gas Hour from 6 to 30 inclusive gives you 25 records per day. If you want to have a time associated with each interval, clean out all the 6's or all the 30's.
Then you can convert the 2 fields to a timestamp with an expression like
TimeDate:[Gas Day]+[Gas Hour]/24

That will give you a sequence that lets you bridge the gap between days.

At this point you have a couple of options. To simplify things for your users you can build a form with a couple of list boxes that let the user pick the starting and ending time and date, and use those listbox values to filter the query. Make sure that the TimeDate column is the bound column.

Also, if you don't have a ID on the table create an Autonumber primary key. That will give you an ascending sequence for filtering records; if you use this option you won't need to create the calculated field and you use this field as the bound column in the listboxes instead.

Denis

 

[Jameo]

Thanks Denis,

The Gas Hours are actually 7-30 - appologies.

I have actually solved this problem a slightly different way. I have kept the Gas Hour and Day the same, and instead have written two different queries.

The first query is as follows. Each field has 3 criterea. For the gas day field:

1) [forms]![frmoutages].[txtdate1]
2) >[forms]![frmoutages].[txtdate1] And <[forms]![frmoutages].[txtdate2]
3) [forms]![frmoutages].[txtdate2]

For Gas Hour:
1) >=[forms]![frmoutages].[cbogh1]
2) (Blank for all)
3) <=[forms]![frmoutages].[cbogh2]


Then my second query is as in my initial post.

My command button then selects the query based on a simple if/else statement:

If Me.txtdate1 = Me.txtdate2 Then
DoCmd.OpenQuery "qryUpdateDeliverability2"
Else
  
   DoCmd.OpenQuery "qryUpdateDeliverability"
   End If

^^^ Just a complete explanation for anyone else suffering the same problem.


Thanks again for your help - got me thinking in the right direction.

Cheers

~Jam