# Solved: Access query: check date against range in table and return value???



## Gram123 (Mar 15, 2001)

Hello,
Long time since I posted here.

Basically, I have an Access database which calculates the cost of jobs by multiplying the number of hours worked by the Labour Rate.
The labour rate changes over time, but we need to keep historical data - i.e. When the labour rate changes I can't just go and change it in the query, I have to amend the formula so it uses the correct labour rate for jobs done on certain dates.

At present, in my query (CALC) I'm using nested Iifs, as follows (note: dates are in UK format):

LabourCost: IIf([JobDate]>=#01/09/2005# And [JobDate]<#01/04/2007#,[HoursWorked]*9.52,IIf([JobDate]>=#01/04/2007# And [JobDate]<#01/06/2007#,[HoursWorked]*9.82,IIf([JobDate]>=#01/06/2007# And [JobDate]<#01/09/2007#,[HoursWorked]*9.85,IIf([JobDate]>=#01/09/2007# And [JobDate]<#01/01/2008#,[HoursWorked]*9.93,IIf([JobDate]>=#01/01/2008# And [JobDate]<#24/01/2008#,[HoursWorked]*11.62,IIf([JobDate]>=#24/01/2008# And [JobDate]<#01/04/2008#,[HoursWorked]*11.68,[HoursWorked]*11.73))))))

The liklihood is that these labour rate changes are going to become more frequent, so I want to scrap the Iifs and use a table instead.

I've already set up a (unlinked) table called LabourRateStd with the following fields:
ID (autonumber)
Date1 (Date)
Date2 (Date)
LabourRate (currency)

The dates and the values have been entered as per the Iif statement above. For the final Date 2, I've just entered a date way off in the future.

So, what I need to do is refer to the LabourRateStd table in the CALC query and retrieve the relevant LabourRate value.

i.e. Where [CALC].[Date] is >=[LabourRateStd].[Date1] And [CALC].[Date] <[LabourRateStd].[Date2], return [LabourRateStd].[LabourRate]

My brain has become fogged and I can't remember how to do this.

Any help is appreciated!

Gram123


----------



## Jimmy the Hand (Jul 28, 2006)

Hi

Based on some SQL experience, I think you'd need somethink like this

```
SELECT [HoursWorked]* (SELECT [LabourRate] FROM [LabourRateStd] 
                       WHERE [CALC].[Date] BETWEEN [Date1] And [Date2])
FROM [CALC]
```
I'm sure this doesn't work, but hope it gives you a clue. 

Jimmy


----------



## OBP (Mar 8, 2005)

Translating Jimmy's SQL to design View of the Query (rather than SQL view) may not be quite so easy to achieve what you want to do by extracting the data from one table to use in a calculation with your other table.
If you can't get it to work can you post a zipped copy of the database with the Table of hourlry rates and the hours worked table with some dummy data in it for us to "play" with.


----------



## Gram123 (Mar 15, 2001)

Ok, here's a stripped down version of the database with a bit of dummy data


----------



## OBP (Mar 8, 2005)

Gram, this appears to be one of those cases where it makes more sense to break the normal rule of Database design of not storing "Calculated" values in the tables.
It is far easier to manipulate the data when the "Labour Cost" is actually calculated at the time the record is stored using the then current labour rate.
Jimmy's grasp of SQL is far better than mine, I usually get there by trying different combinations in Design view and as yet I haven't found one that works. He may be able to do it using "names" in the SQL statement.

I know one way to do it in the Query, but it means using a Called VBA Function. 
Or I can do it using VBA and recordsets.


----------



## OBP (Mar 8, 2005)

OK, I can do it using 2 queries, which Jimmy could probably incorporate in to one Query with a sub Query.
Have a look at this version, I haven't actually checked all the Labour Rates, but it looks right.
The first Calc sets the Rate for an Invoice and then that is used in the Calc query.


----------



## Jimmy the Hand (Jul 28, 2006)

Well, as OBP said, it's possible to do it with one query, though my initial suggestion of a subquery was wrong. I also was't able to make it work by using JOIN operations on the tables. Microsoft Jet engine has a peculiar SQL interpreter, and I couldn't discover the right syntax with inner and outer joins. Thus, I had to resort to a less elegant, but just as effective method: using WHERE clause to join tables.

I used SQL editor to modify CALC, but the resulting CALC2 query is viewable and editable from query designer as well. See the attachment. I left the original CALC untouched, for comparison purposes. 
The record order is a bit different (don't know why), but they return the same rows.

BTW, according to common SQL practice, I used shorts for table names.
E.g.

```
SELECT M.Date, M.RegNumber, M.DepartmentCode FROM Main M
```
is the same as

```
SELECT Main.Date, Main.RegNumber, Main.DepartmentCode FROM Main
```
but it's shorter and makes editing easier.

Jimmy

EDIT
No, they don't return the same rows. There is a row in table *MAIN* with a date of 2000. 05. 31.
This date is not covered in table *LabourDateRangeStd*, and so my query doesn't return it. Put one additional row into table *LabourDateRangeStd* e.g. from 01/01/1900 to 01/01/2005 and that will cover all pre-time dates.


----------



## OBP (Mar 8, 2005)

Jimmy I have added my version to your database and I will keep it for future reference.


----------



## Gram123 (Mar 15, 2001)

Perfect.

I've made the date range amendment that Jimmy highlighted, and I think I'll stick with OBP's 2 query solution, as I understand immediately what you've done, and nothing else in the databse is done in SQL, so it makes it easier to explain to others if necessary.

Many thanks guys!

Gram


----------



## Gram123 (Mar 15, 2001)

I'm having a bit of a problem...

Today I've been applying this to the live database.
As mentioned, I used OBP's method with a FirstCALC query to identify the LabourRate and a CALC query based on FirstCALC and the Main table to add in the other fields.

Based on this CALC query, there are now a host of other queries, for different forms and reports.

However, when I try to enter data in any of the forms, or any of the queries (including the base CALC queries) I am unable to.

On the forms, if I use an OnClick event to go to a New Record, it gives an error saying "You cannot go to the specified record. You may be at the end of the recordset".

In the queries, it won't let me overtype existing data, or enter a new record.

This is a concern, as it is rather urgent I get this database back up and running.
Any help would be appreciated....

====
Gram


----------



## OBP (Mar 8, 2005)

Gram, I am sorry I didn't realise that you wanted to display the data on a form, I thought that this was for reporting purposes.
Both jimmy's amd my versions do the same thing, ie create a Non Updateable record set.
So I think you only have 2 recourses, 
1. Leave it as it is for "Reports" and for forms add some simple VBA to just display the data.
2. Actually put the results of the calculation in to the Table, (see my post #5), this can be achieved in 2 ways, use a query to update all the current records with Historic data and then use soem simple VBA to do the Calculation on the forms as you enter the data.


----------



## Gram123 (Mar 15, 2001)

Ok, I've added a field to the Main table (LabRate) and successfully updated this with the historic data from the firstCALC query.
So now I need to perform this labour rate calculation for each new record so that it updates the Main table.

1) Can this be done in the base query (i.e. what was the CALC query) rather than in each of the forms and reports? As mentioned, each form is based on a query, and each of these queries takes the labour rate info from the CALC query. Aside from getting rid of lots of unnecessary nested IIFs, the other reason for doing this in the first place was to have a "base query" where the calculations were performed, rather than repeating it for each query (or form).

Previously, with the nested IIFs, obviously the value wasn't stored in the table, it was just calculated in the queries.

2) Can it be done without using VBA? If not, can you give an example of the VBA code?


----------



## OBP (Mar 8, 2005)

Gram, it can easily be done using some quite simple VBA code, it should go in both the LabRate and HoursWorked fields After Update Event Procedure, the reason being it will pick up any change that you make in the Labour Rate, but providing that you always change the LabRate first then you can just put it in the Hoursworked field.
It will be something like this
me.laborcost = me.labrate*me.HoursWorked
assuming that laborcost is the field that will hold the result of the calculation


----------



## Gram123 (Mar 15, 2001)

I'm a bit confused. I understand what you're suggesting, but I don't think it does quite what I'm after.

1) The code you suggest works to the point that it will update the LabourCost when the No of Hours Booked (formerly HoursWorked) is changed _for existing records_. This is because I added the values to the LabRate field in the Main table using an update query.
It doesn't currently work for new records, but if I understand you correctly, you're saying that if the user were to manually enter the LabRate value for each record then it will work.
However, my aim was to have the whole labour rate calculation stuff happen automatically, unseen by the user, hence the idea of the LabourRates table with date ranges. The user should be able to enter a Date and the NumberOfHoursBooked and it should return the correct LabourCost by using the appropriate LabRate.

2) I think the code would also need to be added to the AfterUpdate event of the Date field, as this dictates the LabRate value that should actually be used. If a user enters a date incorrectly and has to go back and change it, the LabRate (and therefore the LabourCost) should also update.
I don't think this will work with the code you suggest, as it is based on "Me" (i.e. the values stored in the Main table) rather than calculating based on the LabourRates table.

Thanks for your continued input....


----------



## OBP (Mar 8, 2005)

Gram, I am sorry to have confused you, you are partially correct in what you say about the VBA. Where you are going wrong is with the LabRate table, now that the Calculations have been done, you no longer need the date ranges, just the latest labour rate.
This can be used for all current inputs and when it is time to change it, the VBA will automatically pick up the new rate.
Unless of course you go back in and Change the LabRate after you have made your entries, in which case you just need the LabRate Introduction Date in the table as well with an update query to update the records entered with the wrong Rate.
If the Labrate from the table can't be included in the Form's Query then you can use Dlookup in the vBA to provide it for the Calculation.


----------



## Gram123 (Mar 15, 2001)

Sorry, I'm even more confused now!
I don't understand how your vision of this works! Maybe I've just missed something.

As I understood it, the CALC query results in a Non Update-able recordset, so it can't be used for the forms. Perhaps this is where I went wrong.

Thinking I could no longer use these queries, I added the LabRate field to the Main table and populated it with the LabourRate values that the firstCALC query had returned.

I then went back to my old base query, took out the LabourCost calculated field (the one with the nested IIFs) and replaced it with a field that stores the LabourCost values in the Main table. I applied the code you suggested to one form (there's about 40), but of course it isn't doing anything with the firstCALC or CALC queries anymore.

Therefore, the LabourRates table is completely unconnected to the data being entered in the forms. As far as I can see, the VBA would not be able to pick up / apply new rates. 

Oh, and yes, it is possible that the labour rate will need to be changed for certain records after they have been entered on the forms.


----------



## OBP (Mar 8, 2005)

Gram, can you post what you have now, so that I can look at it fix it for you?
The Calc query did make the Recordset Not Updateable, but you no longer need to use that Query, the calculations have been made and the results are in the Table.
It only needs the VBA to make the calculation on the Data Input Form for new Records.


----------



## Gram123 (Mar 15, 2001)

Attached with some sample data.
Please note, I updated all (100+) queries to use the CALC query.
The only one I have changed back so far is the qAccessoryFit query.

The Base query (old) contains my nested IIFs.

Apologies that it's a bit of a mess....


----------



## OBP (Mar 8, 2005)

Gram, which Form will you be using for the inputting of the Labour Hours which needs the calculation?

Why do you have so many almost Identical Forms rather than one Interactive Form?


----------



## OBP (Mar 8, 2005)

OK, it must be the Accessory Fit form, based on the Query.


----------



## Gram123 (Mar 15, 2001)

Which form will be used to input the labour? All of them! Though, yes, the Accessory Fit form is the one I had changed back to being based on the "Base query" instead of the CALC query. I will have to perform the same task for all of the other forms.

The forms are separate due to an original request from management, and my rather shady database design ability. Originally there were only a few different accounts, and it's grown to the current unweildy proportions.

The user needs to see all records for the specific department when inputting new records, and each form applies the correct department code as a default value for each record on that form, hence the different coloured forms. There are some minor differences too - some forms have an Invoiced Yes/No field, some use different (calculated) Labour Cost and Sales Labour fields etc.

Yes, I'm sure this could be done in a better way, but I simply haven't the time to sort it out at present. It's something that I'll probably have to sort in the long run.


----------



## OBP (Mar 8, 2005)

Gram, I have looked at your database in more detail and I think that you could probably do what your management want with one or two queries, one or two Forms and one or two reports and a Combo to choose which depertment you want to run the Form/report for. All those almost Identical Invoices could be just One Query/report with some simple VBA to change the Colours if you must.
The advantage of this is you would bnot have to Update so much stuff when a change is required.
Anyway back to your current problem.
I have created a new table called New Labour Rates, it just has the one record with the current labour rate and it's date range, but it only actually needs the Introduction Dtae for the current Rate.
I have modified your form so that it calculates the labour Cost when you enter the Booked Labour Hours for a new Record.
I have also created an Update query that calculates all of the previous Record's Labour Costs based on the Rate in the table. Once you have run that Query you can delete the Labour Rate from the Table or leave it in and have the VBA update it when the new record is entered.
Have a look and see what you think.


----------



## Gram123 (Mar 15, 2001)

Hi OBP, thanks for that.
I see what you've done and it works fine for new records (though I can't see your update query).

As mentioned, I can't guarantee that the No of Hours Booked value that has been entered on existing records will stay the same. There may be supplementary work done on a particular vehicle, meaning that value will have to be amended, and the Labour Cost has to reflect that change.
Similarly, it is possible that the Date value on an existing record may need to be changed, so the record would need to pick up the correct LabRate for that date.

If the user changed the No of Hours Booked or Date values for existing records, would they then need to run an Update query to correct the Labour Costs? This isn't ideal, as whilst the form is open, the calculation would appear incorrect.

Can the AfterUpdate code be changed so that:

1) It updates the LabourCost value for old records as well as new records when the No of Hours Booked value is amended?

2) The DLookup looks for the value in the original LabourRates table rather than just looking at the current LabRate from your new table, so that it can be dependent on the date?
If it can't do this, maybe the date ranges could just be hard coded into the VBA? Instead of amending the Labour Rate table(s) when a new rate starts, I'd just amend the VBA. Or is this just the same as using nested IIFs?

Sorry this has turned out to be more awkward than expected....


----------



## OBP (Mar 8, 2005)

Gram, I may have deleted the Update Query.
I don't think the Dlookup can find a value between 2 dates, but a VBA recordset can, I only used the Dlookup to quickly show you that it is feasible to do it this way.
Do you want to leave the LabRate in the Main table?
I can code the AfterUpdate events of both fields so if they are changed it will recalculate the Total.

I really think that you should look at reducing the number of Queries, Forms and Reports that you are using. The maintenance overhead of so many items is horrendous.


----------



## Gram123 (Mar 15, 2001)

The VBA recordset sounds like the solution. My experience of using VB is limited to amending Excel macros (i.e. no programming VB from scratch, but I mostly understand what it's doing when I see the code).

No, there's no need to leave the LabRate in the Main table.
If you can do the coding for the Labour Rate field for non-new records and the Date field that would be great.

Yes, I agree that the maintenance is ridiculous. Prior to setting up the Base Query, each query held (mostly) the same nested IIF calculations, so it used to be even worse.
I'll take a look at the existing queries and see which ones are fundamentally the same, and start thinking about how to simplify.


----------



## OBP (Mar 8, 2005)

Gram, I will post a new version of the form with a Recordset instead of the Dlookup.

I can help you a lot with the Queries, Reports and Forms, and it is not Rocket Science, the Forms and Reports do require a little bit of VBA though.

Do you want to keep all the Form and Report Buttons on the Main forms?


----------



## OBP (Mar 8, 2005)

I forgot to ask was the Booked Hours the correct field for the calcualtion?


----------



## Gram123 (Mar 15, 2001)

Yes:
Labour Cost = No of Hours Booked * LabRate

In other words, the cost of the labour to us is the actual number of hours booked (worked) on each job, multiplied by the labour rate.


There is another similar calcuation on some of the queries:
Sales Labour = No of Hours * SalesLabRate
The price that we charge companies for the labour is the number of hours on an estimate (so it may differ from the time it actually took to do the work) multiplied by an agreed rate.


----------



## Gram123 (Mar 15, 2001)

With regard to simplifying the database, I've gone through the queries and made a list of the differences.

As a start point, the following forms use queries that are fundamentally the same as the Accessory Fitment one you've been working on, though of course with different Deptartment Codes:

Avis (key)
Europcar (key)
National (key)
Promo RUK
REA
Renault RTG
RFS Call Offs
Special Editions
TLS
West Wallasey (key)


One thing all the above have in common is a user-entered Sales Labour value.
For many of the other queries, the Sales Labour rates is calculated within the queries and these calculations differ - some have changed over time, so there's more IIFs. This is because we have different agreements with different companies, and sometimes the rate is changed when a contract is renewed.

Some queries contain fields additional to those in the Accessory Fitment query, some queries use different LabRates, and in a couple the LabRate values are the same as the Sale Labour values, where we make neither a profit nor a loss (e.g. Internal).


----------



## OBP (Mar 8, 2005)

Gram, right here is the latest version witht the new code in the Booked hours and Date fields. It opens a recordset (rs) based on the SQL created by the String starting with SQL = down to the 2 WHERE statements that set the record which has Date1 less than or equal to the current record's Date and Date2 equal or greater than the date.
it sets that to a variable called lr which is used in the calculation
The date version of the code only works when it is NOT a new record and the Hours booked field is not Null.

Step one to help you understand how the Common Query, Form and Report would work I have created a new Form call "Selection Form" and the Query that it opens called "New Query" which is a copy of your Accessory Fit one.


----------



## Gram123 (Mar 15, 2001)

Hi OBP,
Right, I took a look at the Axs Fit form.
If I change the Date on an existing record, the Labour Cost updates correctly.
If I change the No of Hours Booked for an existing record nothing happens to the Labour Cost.
If I enter a new record, when I enter a value in the No of Hours Booked, I get a debug error 3075: "Syntax error in date in query expression 'Date1 <= ##And Date2 >= ##'

One additional note, as each Date2 is the same as the next Date1, the code should be "Date2 >" rather than "Date2>=", otherwise we have certain dates that fit into two different date ranges.

As for the selection form, that's marvellous! I take it we can copy the code and change it to use OpenForm / OpenReport instead of OpenQuery.


----------



## OBP (Mar 8, 2005)

Gram, the Date Sytax error is really wierd as I created the code for the Hours Booked Field first and then copied it into the date field and my version works perfectly for the Hours Booked. 
Can you compare the 2 sets of code for me, copy and paste them on here if necessary.
I can just take out the New Record part of the code for the Hours Booked and it will work both old and new records (when we get it working). 

Yes we can just change the code to open Forms and Reports instead of queries, that was just to show you how it worked.
The next thing is for you to decide how you want to do that part, do you want to stay with all your Buttons or go for the Combo box .
After that I will show you how we can change the Form's and Report's Headings to suit whichever Department you have chosen.
What I would suggest is that you also look at the "other" queries that have a bit more data in them than the ones that you listed in Post #29, because as long as the Query still works, it doesn't matter if it has some extra fields if it will allow you to use it for even more Forms and Reports.

This is the Code that I have that is working OK

Private Sub Date_AfterUpdate()
If Not Me.NewRecord And Not IsNull(Me.No_of_Hours_Booked) Then
Dim lr As Double, rs As Object, SQL As String, recount As Integer, count As Integer
SQL = "SELECT LabourRates.* " & _
"FROM LabourRates " & _
"WHERE Date1 <= " & "#" & Format(Me.Date, "mm/dd/yyyy") & "#" & _
"And Date2 >= " & "#" & Format(Me.Date, "mm/dd/yyyy") & "#"
Set rs = CurrentDb.OpenRecordset(SQL)
lr = rs.LabourRate
rs.Close
Set rs = Nothing
Me.Labour_Cost = Me.No_of_Hours_Booked * lr
End If
End Sub

Private Sub No_of_Hours_Booked_AfterUpdate()
If Me.NewRecord Then
Dim lr As Double, rs As Object, SQL As String, recount As Integer, count As Integer
SQL = "SELECT LabourRates.* " & _
"FROM LabourRates " & _
"WHERE Date1 <= " & "#" & Format(Me.Date, "mm/dd/yyyy") & "#" & _
"And Date2 >= " & "#" & Format(Me.Date, "mm/dd/yyyy") & "#"
Set rs = CurrentDb.OpenRecordset(SQL)
lr = rs.LabourRate
rs.Close
Set rs = Nothing
Me.Labour_Cost = Me.No_of_Hours_Booked * lr
End If
End Sub


----------



## Gram123 (Mar 15, 2001)

This is the code from the version you last posted:

Private Sub Date_AfterUpdate()
If Not Me.NewRecord And Not IsNull(Me.No_of_Hours_Booked) Then
Dim lr As Double, rs As Object, SQL As String, recount As Integer, count As Integer
SQL = "SELECT LabourRates.* " & _
"FROM LabourRates " & _
"WHERE Date1 <= " & "#" & Format(Me.Date, "mm/dd/yyyy") & "#" & _
"And Date2 >= " & "#" & Format(Me.Date, "mm/dd/yyyy") & "#"
Set rs = CurrentDb.OpenRecordset(SQL)
lr = rs.LabourRate
rs.Close
Set rs = Nothing
Me.Labour_Cost = Me.No_of_Hours_Booked * lr
End If
End Sub

Private Sub No_of_Hours_Booked_AfterUpdate()
If Me.NewRecord Then
Dim lr As Double, rs As Object, SQL As String, recount As Integer, count As Integer
SQL = "SELECT LabourRates.* " & _
"FROM LabourRates " & _
"WHERE Date1 <= " & "#" & Format(Me.Date, "mm/dd/yyyy") & "#" & _
"And Date2 >= " & "#" & Format(Me.Date, "mm/dd/yyyy") & "#"
Set rs = CurrentDb.OpenRecordset(SQL)
lr = rs.LabourRate
rs.Close
Set rs = Nothing
Me.Labour_Cost = Me.No_of_Hours_Booked * lr
End If
End Sub


----------



## Gram123 (Mar 15, 2001)

Ok, the queries.
Currently, every account has 1 query for it's form and at least 1 query for it's report.

I'll call those mentioned in post 29 "Standard". Those queries all contain the same fields. The report queries for those accounts are very similar to the form queries except they have "first and last" parameters for the Date field and the Invoice Number field.

2) Then we have these accounts which include the field called Invoiced (a Yes/No checkbox).
Each of these accounts has the form query and 3 report queries ("invoiced", "not invoiced" and "both"):
- Ext Recharge
- Leasedrive
- Mission To Seafarers
- Staff Sales

3) This one includes the Ply field (a combo linked to the Ply table).
- Ply
This has 6 report queries, one for each type of Ply in the combo (Ply Eurodrive, Ply Misc, Ply National, Ply RBF, Ply Thrifty):

4) These include an additional linked table "Dual Control", which uses different Labour and Parts costs and it includes the Dual Control "Model" field
- Dual Control
- Dual Control RBF

5) These are End User accounts and use a completely different set of Labour Rates to calculate the Labour Cost:
- DVD Fitment
- Phone Fitment
- Vardy
The current calculation is as follows:
Labour Cost: IIf([Date]>=#01/09/2005# And [Date]<#01/04/2007#,[No of Hours Booked]*9.52,
IIf([Date]>=#01/04/2007# And [Date]<#01/06/2007#,[No of Hours Booked]*9.82,
IIf([Date]>=#01/06/2007# And [Date]<#01/09/2007#,[No of Hours Booked]*9.85,
IIf([Date]>=#01/09/2007# And [Date]<#01/01/2008#,[No of Hours Booked]*9.93,
IIf([Date]>=#01/01/2008# And [Date]<#24/01/2008#,[No of Hours Booked]*9.51,
[No of Hours Booked]*9.53)))))

6) On these, instead of the user entering the Sales Labour value, it is calculated in the query, using the same rate as the Labour Cost value (i.e. Sales Labour: [No of Hours]*[LabourRate]):
- Co Cars
- Internal

7) Finally, we have a set of accounts where instead of the user entering the Sales Labour values, they are calculated in the queries according to their contracts. Some of these also include the Invoiced field as per the first lot mentioned in this post:

7.1) Sales Labour:[No of Hours]*22
- Cleveland Police
- Police Striping

7.2) Sales Labour:[No of Hours]*22 + Invoiced field
- Apostles
- Dial A Ride
- Harbour Police
- United Rental

7.3) Sales Labour: IIf([Date]<#23/11/2006#,[No of Hours]*22,[No of Hours]*22.65)
- Thrifty

7.4) Sales Labour:[No of Hours]*22.5 + Invoiced field
- RFS

7.5) Sales Labour: [No of Hours]*23 + Invoiced field
- West Wallasey

7.6) Sales Labour:[No of Hours]*23.3 + Invoiced field
- Eurodrive

7.7) Sales Labour:[No of Hours]*23.5 + Invoiced field
- Quartz

7.8) Sales Labour:[No of Hours]*25
- ETS

7.9) Sales Labour: IIf([Date]<#01/04/2004#,[No of Hours]*20.5,[No of Hours]*25)
- Dealer (historic data only)

7.10) Sales Labour: IIf([Date]<#01/09/2005#,[No of Hours]*15.93,IIf([Date]>=#01/09/2005# And [Date]<#01/09/2006#,[No of Hours]*21.75,[No of Hours]*21.57))
- Marine

7.11) Sales Labour: IIf([Date]>=#01/09/2005# And [Date]<#01/04/2007#,[No of Hours]*9.52,IIf([Date]>=#01/04/2007# And [Date]<#01/06/2007#,[No of Hours]*9.82,IIf([Date]>=#01/06/2007# And [Date]<#01/09/2007#,[No of Hours]*9.85,[No of Hours]*9.93)))
- PDI
- Phase 2

7.12) Sales Labour: IIf([Date]<=#19/12/2007#,[No of Hours]*25,[No of Hours]*28)
- Transport


----------



## OBP (Mar 8, 2005)

Gram, I have just pasted your and my date Code for both lines one after the other and they are all identical, so how can yours work for the Date and not the Hours Booked when mine works for both? 
Can you try copying it from the Date field and overwrite that part of the code in the Booked Hours field, I would copy and paste the whole section like so.

SQL = "SELECT LabourRates.* " & _
"FROM LabourRates " & _
"WHERE Date1 <= " & "#" & Format(Me.Date, "mm/dd/yyyy") & "#" & _
"And Date2 >= " & "#" & Format(Me.Date, "mm/dd/yyyy") & "#"

Now to those queries, can you add some fields to the Labour Rates Table that will hold the Special labour rates for the other queries?
Also add a labourrate field to the Department Table that will hold the 22, 22.5, 23, 23.5 etc
We can handle the "("invoiced", "not invoiced" and "both")" with an Option Group on the form unless you want to continue using your Buttons


----------



## OBP (Mar 8, 2005)

Gram, we can actually use this code

Private Sub No_of_Hours_Booked_AfterUpdate()
Dim lr As Double, rs As Object, SQL As String, recount As Integer, count As Integer
SQL = "SELECT LabourRates.* " & _
"FROM LabourRates " & _
"WHERE Date1 <= #" & Format(Me.Date, "mm/dd/yyyy") & "#" & _
"And Date2 > #" & Format(Me.Date, "mm/dd/yyyy") & "#"
Set rs = CurrentDb.OpenRecordset(SQL)
lr = rs.LabourRate
rs.Close
Set rs = Nothing
Me.Labour_Cost = Me.No_of_Hours_Booked * lr
End Sub


----------



## Gram123 (Mar 15, 2001)

Actually, I think I know what was going on with the previous code.
When I was entering that record, I put a dummy Invoice Number in (the primary key field) and then entered the No of Hours Booked, without putting a Date in first, hence the debug error...

Anyway, I pasted in your new code, firstly just on the No_of_Hours_Booked_AfterUpdate() and later also on the Date_AfterUpdate(); and did some testing.
The Labour Cost successfully updates if the Date value is changed.
If you just change the No of Hours Booked it doesn't update the Labour Cost. However, if you then change (or re-enter) the Date it then takes the No of Hours Booked into account.
In other words, if you amend the No of Hours Booked, it won't affect the Labour Cost unless you also amend the Date.

Also, I got another Debug error when inadvertently entering a date outside of the ranges in the LabRates table, so I amended the first Date1 to the year 1900 and that resolved that.

I'll hang fire on amending the tables for the special labour rates and such...


----------



## OBP (Mar 8, 2005)

Gram, did you remove this line in 
If Me.NewRecord Then
and the 
End If


Do you still want to go ahead with the Common Queries?


----------



## Gram123 (Mar 15, 2001)

Ah, the Event Procedure had gone missing from the No of Hours Booked field on the form. Fixed now, and all seems to be working.

I will add the field to the Labour Rates table now.

I'm going to put the 22, 22.5, 23, 23.5 rates into a separate table, as these are Sales Labour (not Labour Cost) rates, and it will avoid confusion in the future.


----------



## Gram123 (Mar 15, 2001)

I've added EndUserRate to the LabourRates table, and I've added a new table for the Sales Labour Rates of external companies, with values entered for the various date ranges.

So just to recap / summarise,
Everything uses the original LabourRate from the LabourRates table, except for these 3, which use the EndUserRate from the same table:
- DVD Fitment
- Phone Fitment
- Vardy

For these 2, the Sales Labour matches the Labour Cost, so it's [No of Hours]*[LabourRates]![LabourRate]:
- Co Cars
- Internal

These 6 use the Sales Labour rate called SLStdExternal from the SalesLabourRates table:
- Cleveland Police
- Police Striping
- Apostles
- Dial A Ride
- Harbour Police
- United Rental

The following have their own specific Sales Labour rates from the SalesLabourRates table:
- Thrifty
- RFS
- West Wallasey
- Eurodrive
- Quartz
- ETS
- Dealer
- Marine
- Transport
- PDI and - Phase 2 (these 2 both use the same rate)

The Sales Labour for any other account in the database does not come from a pre-defined sales labour rate, it is manually entered by the user.

Phew! Hope this makes sense...


----------



## OBP (Mar 8, 2005)

Gram, is the "Co Cars" in "For these 2, the Sales Labour matches the Labour Cost, so it's [No of Hours]*[LabourRates]![LabourRate]:
- Co Cars
- Internal"

actually CAT Company Cars - Dept 4.9

It would probably have been better to have used the Department Numbers for identifying the differences but never mind.

To demostrate how one or two forms will work for you I have copied your Accessory Fit Form and called it "All Dept Form" and modified your Switchboard's first 2 Buttons to open that form. I have added a "Department" field to the Switchboard to trigger the necessary changes to the "All Dept Form"
You will see that it opens the Form with correct Title for the Button clicked.

Have a look and see what you think, if you want to change the Colour of the Back/Fore Ground of Label28 we can do that as well, if you look at the VBA Code on the Switchboard and the new Form's "On Current" event procedure you will see how easy the changes are to achieve.

So you need to make the decision on whether to go with your Current Switchboard or just use a Combo Box to select the relevant Dept.


----------



## Gram123 (Mar 15, 2001)

Yes, sorry, by Co Cars I meant CAT Company Cars.

I've just learned that I made a slight error with the Sales Labour Rate for Phase2 and PDI. These should actually be the same as Internal and Company Cars. The reason I was wrong is because they haven't been used for some time, but wouldn't you know it, the user is going to start re-using (at least) one of them again.
I'll amend this when I take a look at your update after lunch....


----------



## Gram123 (Mar 15, 2001)

Ok, I see how it works, and it looks nice and peasy so far!

The user would rather have the buttons on the switchboard for the time being, due to concerns over selecting the incorrect forms (or particularly reports) from drop-down lists.
Unfortunately I didn't ask them about the form colours, and they've gone home, so I'll have to wait until tomorrow to get that info. Personally, with a big obvious title at the top, I don't see a need for different coloured forms, unless they mean more than just differentiation.

I made the change to the SalesLabourRates table, removing Phase2 and PDI, and the redundant date ranges, and have attached the amended version.


----------



## OBP (Mar 8, 2005)

Gram, staying with the same theme, I have combined the 2 concepts of the Combo selection of the department and the Switchboards Buttons.
I have made the Department Table the Data source for the Switchboard, the 2 fields can be set to Invisible, they are just showing at the moment so that you can see them work.
I have modified the "New Query" so that it takes it's Criteria from the Switchboard's Department ID instead of the "Selection Form's Combo".
I have changed the All Dept Form's data source to the "New Query", (I hope that is providing the correct data, it seems to work).
I have changed the VBA for the Switchboard and the All Dept Form so that it is now all automatic, Heading and Data all change to reflect the chosen Department.
I have also taken the liberty of adding some "friendly" VBA to your Switchboard's Quit button, you don't have to use it, but you might find it useful in the future as it provides interaction with the User.

Now for the big question, do you think you can copy and paste the New Switchboard Button's VBA to the others that should use this Query and Form, you only have to change the Dept number in this line for each one to make it work.
Once you have done so and it is all working OK you can delete those other Forms and their queries (as long as they are not supplying any reports with data) and make the database quite a bit smaller.


----------



## Gram123 (Mar 15, 2001)

Hi OBP, I was off work sick for most of last week, so have only just got back and got round to doing this.
Here's what I've done this morning:

- Copied your VBA from the Accessory Fit button to the buttons for Avis (key), Europcar (key), National (key), Promo RUK, REA Group, Renault RTG, Special Editions, TLS & West Wallasey (key).

- For those accounts I deleted the old forms, associated queries, and the macros which used to open the forms from the switchboard. I also replaced the macros that used to go to a "new record" on each form, with a single one for the All Dept Form. 

- Removed the code from the CAT Company Cars button - you'd used this for testing, but it doesn't use the standard rate as per Accessory Fit and the others noted above.

- Removed the Caption from the All Dept Form, as it was saying Accessory Fit, regardless of the actual department.

- Removed the default value of 4.3 (the code for Accessory Fit) from the Department Code field, as this would have wrongly been assigned to new records. Can we make this field default to the correct value, dependent on which button the user has pressed?

- Added new labour rates which started on June 1st.

RFS Call Offs would have been the same as the above accounts, but it is apparently no longer in use, so there's no button for it on the switchboard, so I've just left this form as is for old data.

I imagine the next lot to deal with are those with the same basic form, but which also have the additional field "Invoiced".

These are:
- Ext Recharge
- Leasedrive
- Mission To Seafarers
- Staff Sales

Are we creating a new form for these, or using the same form, but supressing that field on the 'standard' accounts noted at the top of this post?


----------



## OBP (Mar 8, 2005)

Gram, are all those with the same account working OK?
Yes we can do something with default value for the Dept. based on the switchboard selection.
I think we can use the same form and have the other rate in the same place as the "Standard" one and make them Visible/Invisible as required. 
I am a bit busy at the moment so I am not sure how quickly I can get around to it though.


----------



## Gram123 (Mar 15, 2001)

Yes, they all appear to be working correctly.
No worries if you're busy, I'll crack on with the next bit.

I'll add the Invoiced field to the All Dept Form, and I'll copy the code over for the 4 accounts noted above that use it. Just to clarify: these use the same labour rates as the ones that have already been done.

I absolutely don't mind having multiple forms for those accounts that do use different labour rate calculations. In fact it would probably make it easier.

Oh, and I checked with the user about the colours of the different forms - they don't care in the least, so there's no need to do anything with that.


----------



## OBP (Mar 8, 2005)

Gram, there isn't much point in having more than one form, but it is up to you, it is your database after all. 
The more that you have the more you have to maintain.


----------



## Gram123 (Mar 15, 2001)

Ok, well I've done as I said I would for the 4 with the Invoiced field, and again, I've deleted the old forms, queries and macros for them.

The way I envisioned it was I'd have several forms:
- "Standard" - as above, using the normal labour rate
- End user - using the alternative labour rate
- Ply - because it has a field identifying the company
- Dual control - because it has a field identifying the model and uses cost fields from the Dual Control table
- Internal/Company Cars - because it has calculated labour and sales labour rates 
- Calculated - because they all use some calulcation or other for the labour

Any new accounts should fit into one of the above.

I don't know how visible / invisible controls work exactly, but for Ply and Dual Control, there are pretty wide combo boxes. If this same form was used, would there be a blank space when opening for other accounts?
It doesn't matter so much for the Invoiced control as this belongs on the far right.

And having two or more fields on top of one another, with only one visible seems more of a mess about than having a separate form with just the relevant controls.
Maybe I'm wrong, but it seems less complicated with more forms!
Anyway, I'm happy dealing with maintaining 6 forms, rather than the original 31!


----------



## OBP (Mar 8, 2005)

Gram, I have added the VBA code to make the Invoiced field and label Visible for the 4 Departments.


----------



## Gram123 (Mar 15, 2001)

Thanks for that, works like a charm.

Had a bit of a re-think and...
As I sort of understand the code you've added to the Date and No of Hours Booked controls on the All Dept Form, I figured I'd have a go at amending it for use with the End User accounts (rather than creating a new form for these 3).
I think I've done it correctly, and it seems to work as expected, but I'd appreciate it if you could take a quick look.
Basically, I added an If Then Else clause to the code on each control, so if it's an end user account, use the EndUserRate, else use the standard LabourRate.

And again, I deleted the old forms and other objects.

If that looks ok, I'll have a crack at doing the Internal / Company Cars ones next.


----------



## Gram123 (Mar 15, 2001)

Ok, I think I've done Internal and Company Cars too, by editing the code on the Date to add an additional If Then Else, and by adding code onto the No of Hours control to effect the Sales Labour figure.

So I guess the next thing is to tackle those accounts with the individual Sales Labour rates. I think I can do this, but it will have to wait until tomorrow.

Then it'll be the accounts that include different fields (Ply and the 2 Dual Control accounts), which I'm not sure how to deal with just yet, and am still thinking separate forms may be the best option...


----------



## OBP (Mar 8, 2005)

Gram, your VBA and confidence is really coming on to tackle it yourself, that is great. iw ill have a look tomorrow.


----------



## OBP (Mar 8, 2005)

Gram I have looked at it and it looks fine.
Are you working on the Ply and Dual Control accounts?


----------



## Gram123 (Mar 15, 2001)

I started but have just confused msyelf!

Ok, I amended the New Query to include the Dual Control.Model and Ply.Company.

I then copied the All Dept Form, creating the Dual Form and Ply Form. I removed the Reg Number control from both, and added in the required controls. I edited the event procedures on the switchboard for the two buttons to open the correct forms, and deleted the old forms queries and macros.

That should be sufficient for the Ply form, as it is "normal" other than having the Company field instead of the Reg No - i.e. it uses the standard labour rate, doesn't use the Invoiced field etc

However, the Dual Control one is different. It should still use the standard labour rate (calculateing the Labour Cost based on the Date and No of Hours Booked).
It should also enter the Sales Labour by selecting the relevant value from the Labour column in the Dual Control Model table (dependent on the Model the user selects); and enter the Parts Sale by selecting the relevant value from the Parts column in the same table.

I started trying to write the code for this, but was then informed that over time, the values in the Dual Control Model table for Labour and Parts could potentially change. In other words, the Sales Labour and Parts Sale figures would be dependent on both the Model and  the Date.

There is only one Labour (Sales Labour) and one Parts (Parts Sale) value per model at present. I figured I'd better add in a Date1 and Date2 field to the Dual Control Model table too, to cover the eventuality of new values in the future.

However, I'm doubting the table design and my brain's gone a bit fuzzy with regard to the code. Maybe you could take a look?

The button on the Switchboard is marked "AXS Dual Control", BTW.


----------



## OBP (Mar 8, 2005)

Gram, as this Form only applies to the Dual Control Department I take it that you no longer need the VBA code to test for the Other Departments?
ie, it can only be Dept. 1.1


----------



## OBP (Mar 8, 2005)

Gram, you don't need a Recordset to put the Labour (Sales Labour) and Parts (Parts Sale) values in the the Form, just some VBA added to the select a Model Combo.

I have done so on this version, but I am not sure about the other VBA code perhaps you could look at that decide which is corrrect?


----------



## Gram123 (Mar 15, 2001)

Hi OBP,
Your code on the Dual Control Model updated the Parts Cost - this should have been the Parts Sales, so I've amended that.

I removed the code from the No of Hours field on that form - it doesn't matter how many hours were taken on a Dual Control job, we still only charge the customer the set amount as per the Dual Control Model table.

I also removed the Invoiced field from this form.

I then tested what would happen if new costs were added to the Dual Control Model table for one of the models, applicable for a certain date range - and it goes to pot. This is what I meant when I said I was doubting the table design.

I might be wrong, but I'm thinking the way round this is to have a Dual Control Model field in the main table with a datatype of Lookup. Then the Dual Control Model table would contain just the models and another table would contain the date ranges, sales labour and parts sales values. I've added such a table, called Dual Control Prices.
This way, I'm thinking the code on the Model control would need to consist of If Then Else's, so when the user selects a model, it look up the values in the Dual Control Prices table and enters them on the form.

This might not be the best way round the problem, so if you have any thoughts, let me know!

Oh, and thank you so much for your continued support!!


----------



## OBP (Mar 8, 2005)

Gram, you can go down that route if you want to, but it is not necessary, I just forgot that we were using a Continuous Form and queries are confused by them. I added a Text box to the header section called "currentdate" and now the Combo works fine for selecting the latest model and prices etc.


----------



## Gram123 (Mar 15, 2001)

It doesn't appear to be working.
In the Dual Control Model1 table, for the model Clio 3, we have Labour value of £63.66 and Parts value of £296.34 for any date up to 31/12/08.
For dates after that, we have a Labour value of £0 and Parts value of £300.

If I edit a record or add a new one, it doesn't matter what date I put in, it only brings up the first set of values in the Sales Labour and Parts Sales controls.
I also noticed that when you click the drop-down arrow on the Model control, the date range displayed for Clio 3 is the first date range.


----------



## OBP (Mar 8, 2005)

The only data range should be the date range that matches your Record's "Date field", at least that is how it worked for me. There were 2 Clio 3 reocrds, but it only displayed 1.

No you are right it is not working properly, it is only showing one record, but it is the wrong one. 
I will get back to you.


----------



## OBP (Mar 8, 2005)

Gram, OK, it works when the Combo is refreshed to pick up the Current Records date, but because it is a continuous Form it loses the display for the "older dates", because they are no longer in the Combo's recordset.
So it looks like it is back to using VBA and recordsets.


----------



## OBP (Mar 8, 2005)

Gram, I agree that splitting the Tables in to Models and prices is the best route.
But the design of you new table does not lend itself to Updating so I have used the Structure of the Original Dual Control table but linked the 2 tables via the DualControlModelID fields.
I have created the VBA SQL Recordset to look up the prices and put them in the Labour and parts fields. I have left a message box in place that tells what prices it found.

You now need a Form/subform to maintain the Model prices.


----------



## Gram123 (Mar 15, 2001)

I copied the code from the AfterUpdate from the Dual Control (Model) field and added it to the AfterUpdate of the Date field as well, so when you change the date on an existing record, it updates the values accordingly.

When you say a form/subform is required, I'm guessing that this is just to make it easier to update than it would be to amend the two Dual Control tables directly.
I've done a quick form (Dual Control Model) and subform (DualControlPrices Subform), and added a button to the Switchboard for now.
If this isn't what you meant, do tell me!


----------



## OBP (Mar 8, 2005)

Gram, that is precisely what I meant, it is for other users, just in case you are not available to do it. 
What is next, more forms or on to all those Reports?


----------



## Gram123 (Mar 15, 2001)

There's a couple of things before we get to the reports!

- The form and/or code for those accounts that use the different Sales Labour values from the SalesLabourRates table.

- Something to set the Department for new records (currently you have to manually enter the Dept code for new records).

There was another small task, but it escapes me for the moment...

Then I guess it's on to the reports...
Oh, and as the user has been using the old format database in the interim, so at some point all that data is going to have to be transferred into this one, which mightn't be easy, as most were using IIFs in the queries rather than storing the values in the Main table.


----------



## OBP (Mar 8, 2005)

Gram, can you outline for me which form you want to use for the different Sales Labour values from the SalesLabourRates table, which fiields need the Code and which fields need updating with the Code with those which fields in that table?

The Set the department for new records is no problem.
The later records should transfer over OK providing there hasn't been a Price increase during the tiem we have been working on this.


----------



## Gram123 (Mar 15, 2001)

Ok, before we go on, I've done a bit of housekeeping, deleting all trace of 3 long-discontinued accounts (Dealer Damage, PDI and Quartz), which also simplifies the SalesLabourRates table as there are now fewer date ranges.

I learned that Marine Phase 2 is being re-introduced and will use the same rates as Internal, so I've sorted that one.

So...
The following accounts may as well use the same form as the "standard" one used for Accessory Fit etc etc (or a copy thereof).

They all need to update the Labour Cost based on the Date and the No of Hours Booked, using the standard LabourRate value in the LabourRates table.

They also need to update the Sales Labour based on the Date and the No of Hours, using the appropriate rates from the SalesLabourRates table.

Cleveland Police, Police Striping and Marine do not need the Invoiced field to be visible, all others listed do. I've included the Dept codes in brackets next to each account.

The following use the SalesLabour rate called *SLStdExternal* from the SalesLabourRates table:
- Cleveland Police (3.7)
- Police Striping (2.7)
- Apostles (4.5)
- Dial A Ride (3.9)
- Harbour Police (3.8)
- United Rental (8.7)

These use the Sales Labour rates noted:
- Thrifty = SLThrifty (8.6)
- RFS = SLRFS (8)
- West Wallasey = SLWestW (8.8)
- Eurodrive = SLEurodrive (8.4)
- ETS = SLETS (3.6)
- Marine = SLMarine (2)
- Transport = SLTransport (9)


----------



## OBP (Mar 8, 2005)

Gram, I was working on the requirements above when it suddenly struck me that once we have use the Switchboard to open the appropriate Department we don't actually need to refer to in the VBA code as we only need to refer to the Department Code field.
It saves a lot of "space" in the VBA commands.


----------



## OBP (Mar 8, 2005)

Gram, can you have a look at this version of the database.
It has the automatic Department Code for new Records.
The new "Sales Labour based on the Date and the No of Hours, using the appropriate rates from the SalesLabourRates table" VBA code on the No of Hours Field's After Update.
can you check that it does what you want?


----------



## Gram123 (Mar 15, 2001)

Hi OBP, thank you for that. I will take a look at your changes at some point today.

I have a separate (but quick) VBA question, I wonder if you might be able to help me with:

I have a database which monitors certain vehicles arriving on site. The same vehicle comes in multiple times, and each visit by a vehicle is one record. 
There is a button that you click to Find a record on the Reg Number field. However, when you enter a Reg Number and click Find Next, it cycles through the records in order from oldest to most recent visit. I want to edit the code so that it cycles through the records in reverse order. 
The user can do this manually on the Find and Replace dialog by selecting "Up" in the Search box, rather than the default "All". However, this doesn't save, so when the database is shut down and restarted it defaults back to "All".

I'm hoping this can be done by adding a single line of code?

Thanks.


----------



## OBP (Mar 8, 2005)

Gram, can you post the code that it uses?
If the code is based on a Query you can just sort the query by date in descending order.


----------



## Gram123 (Mar 15, 2001)

The code on the button for finding the reg number is as follows:

Private Sub Command85_Click ()

OnError GoTo Err_Command85_Click
Me![Reg Number].SetFocus
Do.Cmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_Command85_Click:
Exit Sub

Err_Command85_Click
MsgBox Err.Description
Resume Exit_Command85_Click

End Sub


----------



## Gram123 (Mar 15, 2001)

Back to the main task...

- Yes, the Department Code is now auto-entering correctly for new records.

- Yes, the code you added on the AfterUpdate of the No of Hours field for those accounts that use the SalesLabourRates table values works correctly.

- I've copied that code to the Date field's AfterUpdate event, so again, when you change the date on an existing record it finds the appropriate Sales Labour value.

- I've amended the code on the form so that the Invoiced field appears on all of the required accounts.

- I've changed all of the buttons on the switchboard so they now open the All Dept Form and I've deleted the old forms, form-queries and associated macros.

The database is looking *much* slimmer now!


----------



## OBP (Mar 8, 2005)

Regarding the Find a Reg Number, what do you actually want it to do?
Would you like it to go straight to the last occurence of that reg and display that record?
What do you do with the record once it is displayed?
Would it matter if it displayed it as a single record on a seperate form?

Back to the Main Task, what is next?
Being that much "slimmer" It should mean a lot less maintenance for you


----------



## Gram123 (Mar 15, 2001)

The Find a Reg Number - It is not currently based on a query, just on a table. There is a query which contains all fields, with Date in Descending order, but this is no good as we don't want the records to be in reverse order on the form. It is a Continuous Form, displaying one record at a time.

What I need the button to do is to go to the last occurence (i.e. most recent visit) of the user-entered reg, and if that's not the right record, the user should be able to click "Find Next", and it will find the last but one occurence and so on.



As for the Main Task, I guess it's time to do something with those numerous reports and their queries...
Currently there is one standard report for each account. Each of these reports is based on it's own query. The queries were basically the same as the old form-queries, with a couple of Criteria thrown in - they all included those IIF calculations, the same as the form-queries did, because back then, the Sales Labour, Labour Cost and Labour Rate values were not stored in the MAIN table as they are now.

The Criteria consists of Between "First" and "Last" parameters on the Date and Invoice Number fields, with something to identify the Department Code.

In addition, those accounts that have the Invoiced field appearing on the forms, they have 2 additional reports (and therfore queries), one for Invoiced = Yes, and one for Invoiced = No.


----------



## OBP (Mar 8, 2005)

I think that you can probably reduce it to just one or 2 Queries and reports.

I don't know if there is a sort order in the DoCmd.menuitem.
Wouldn't it make more sense to have all the records in descending date order?


----------



## OBP (Mar 8, 2005)

Gram, I couldn't find anything on setting the Docmnd "Find" direction, however if you have 2 buttons you can use the first button's code to find the Last record and then the Second button's code to find the "Previous" record.

Public Rs As Recordset, srtfind As Integer
Private Sub Command12_Click()
strfind = Me.[Reg Number]
Set Rs = Me.RecordsetClone
Rs.FindLast "[Reg Number] = " & strfind
Me.Bookmark = Rs.Bookmark
Rs.Close
Set Rs = Nothing

End Sub

Private Sub Command13_Click()
strfind = Me.[Reg Number]
Set Rs = Me.RecordsetClone
Rs.FindPrevious "[Reg Number] = " & strfind
Me.Bookmark = Rs.Bookmark
Rs.Close
Set Rs = Nothing

End Sub


----------



## Gram123 (Mar 15, 2001)

Ok, I'm going to leave the Reg finder thing as it is. It's just as easy for the users to manually change the search direction themselves, as it would be to have 2 separate buttons. And unfortunately, putting the records in reverse order wouldn't really work, as they wouldn't sort themselves until after the form is closed and re-opened. But thanks for looking into it, anyway.

How do we start on the queries / reports for the main task?


----------



## OBP (Mar 8, 2005)

Is there anything in the QAccessoryFit Report that is not in the Base Query?


----------



## Gram123 (Mar 15, 2001)

As it stands currently, the only things in the QAccessoryFit Report that are not in the Base Query are these calculated fields:

Total Sales Cost: ([Sales Labour]+[Parts Sales]+[P+M])
and
Profit: ([Total Sales Cost]-[Total Costs])

However, they don't need to be, as these calcuations are done in your New Query (which the All Dept form is based on), and could also be done on an equivalent All Dept Report (or the query the report is based upon).
The components of these calculations (i.e. Sales Labour, Parts Sales and P+M) are in the Base Query.

The reason the calculated fields were not in the Base Query is because most of the account queries calculated Sales Labour using IIFs, but Axs Fit is one of the few accounts where the user could just enter a value for Sales Labour, which would be stored in the Main table, so it was technically a different field.

Beyond that, the only difference is the criteria.


----------



## OBP (Mar 8, 2005)

So, if we use a copy the "All Dept form" Query for the reports, for those depts that use the All Dept form you would not have much work to do, just add the criteria to it.
The Report Headings can be handled the same way as the Form's heading.


----------



## Gram123 (Mar 15, 2001)

Right:

- I made a copy of the "New Query" (the query for the All Dept Form) and called it "Report query", adding in the criteria.

- made a new report called All Dept Report, and set the source to be the "Report query".

- added the Dept fields to the foot of the Reports form, and set the record source of the form to be the Department table.

- copied the OnClick event code for the Accessory Fit button from the switchboard, and added this to the button that opens the Accessory Fit Report, on the Reports form. Amended the code so it opens the All Dept Report (in print preview), rather than opening the form.

- copied the bit of code from the All Dept Form's OnCurrent event that sets the title, and applied this to the OnOpen event of the All Dept Report.

- all this seemed to work ok, so I went ahead and did the same for all relevant accounts.

- deleted the old reports and their queries for those accounts, plus the button macros that opened the old reports.


So that's reduced the database by another chunk (28 queries and 28 reports)!

I haven't done anything with the AXS Dual Control report, or the Ply Line reports yet - these were the 2 that used different forms.

I also haven't touched the "Invoiced" and "Not Invoiced" reports, or their associated queries and buttons on the Reports form.


----------



## OBP (Mar 8, 2005)

You don't need my help anymore, you have it sussed. 

You can just do the same for those final depts, you can either use the same report and control the fields that show or have just one more report for them.


----------



## Gram123 (Mar 15, 2001)

Nearly there now...

I've created separate queries & reports for Dual Control and Ply, with the latter being a combined report grouping on the company, rather than 5 or 6 separate ones.
I've also created queries and reports for Invoiced = yes and Invoiced = no. There may be a better way to do this with code, but it works fine, so I'm happy to leave it as it is.

Then it was onto tidying up...
- I noticed that the Ply form wasn't picking up the Department code on new records, so copied your code from another form.
- I've corrected some labour rates and such to how they should be.
- I made the Department fields invisible on the foot of the Switchboard and Reports form.

- Everything redundant has been deleted. There are a few queries which I'm not sure if we need to keep hold of for now - namely the two DualControlModelPrices queries, plus CALC, firstCALC and Calculate Labour Cost (the update query).


The final stage will be to get the current data and transfer it into the new version of the database. Presumably we need to use update queries to perform the calculations and then store the relevant labour values etc in the Main table. 
I need to try and get this done next Monday, so if you could help me one final time next week, it would be greatly appreciated.

In total, we've reduced the number of forms from 40 to 8, queries from 122 to 15 and reports from 74 to 5, plus something like 90 macros have been deleted!!

I tried to upload the latest version of the db, but it keeps failing for some reason, so I'll do so later.


----------



## OBP (Mar 8, 2005)

Have you Compacted and repaired it?
You can overcome the need for 2 queries/reports for Invoiced "Yes/No" by having 2 criteria lines and an "Option" Column which is set by the Form.
line one would read "Yes" for Invoice Option = 1 and "No" for the Invoice Option = 2.


----------



## Gram123 (Mar 15, 2001)

Hi OBP, I should have the data from the old database within the next half hour, which then needs to be transferred the new VBA-tastic version, and then all is finished with this thread!

I'll get the existing data into the new version, and then I guess I'll need to use update queries to get the Labour Rate into the Main table, plus calculate and save the Labour Cost and Sales Labour values, as per the calculations done in the VBA. I'm not sure how I go about this...

In answer to your last question, yes, I compacted and repaired, and the db is set to "Compact on Close". The latest copy including the changes I made on 30th June has successfuly attached to this message.


----------



## Gram123 (Mar 15, 2001)

I've now moved the data from the old to the new database, and stripped out all old defunct accounts (the old database can serve as an archive).

I had a thought - is it possible to simply make all records on the forms "requery"?
Even if it's something I have to do for each form, it would be pretty quick and should update all values as required, rather than having to use complex update queries.

What I mean is, as I've imported the data from the old database, the Labour Cost figures and (most of) the Sales Labour figures are showing as zeroes. If I go to a record and overwrite the value in the Date / No of Hours / No of Hours Booked field, it recalculates because of the VBA and saves the value to the table. 
If we could force all values to recalculate, it should store all the appropriate values to the table.

In a related point, there are 3,500 or so records where Sales Labour was manually input and was stored in the old database's Main table. If I go to a form where the Sales Labour was manually input in this manner (e.g. Accessory Fit) and overwrite value in either the Date or the No of Hours field, with the same value, it sets the Sales Labour figure to £0.00. I guess this can be fixed with a quick amendment to the code on the AfterUpdate of those fields.


----------



## OBP (Mar 8, 2005)

Gram, how long do you have to convert the data?


----------



## Gram123 (Mar 15, 2001)

Ideally, it needs to be done today.
I'm on holiday for the rest of the week and the user will need to use the database from tomorrow...

If you can't spare the time today, I guess I could leave it until next Monday, but I'd need to re-import the Main table then, as there will be new data.


----------



## OBP (Mar 8, 2005)

Gram, can you arrange with the User to have it on a Friday or Saturday to give us a bit longer. I will create some VBA to work on a form that will convert the whole table.
We should have done this before now. 

The user can enter data in to the new database in the mean time, we just need to convert the old data for reports etc.


----------



## Gram123 (Mar 15, 2001)

Ok, I will submit the current version to the user now, and instruct them to use it for adding new data, but to leave the old data as is.

I can't attach a copy here with complete data, as it exceeds the max file size for a 7zip. I can either email you a copy, or I could upload the data separately (in an otherwise blank db) if required.

This gives until next Monday to get the values correct, and I'll just copy any new data over next week to finalise it.


----------



## OBP (Mar 8, 2005)

Gram, I have pm'd you my email address.


----------



## Gram123 (Mar 15, 2001)

All remaining issues and amendments were made via email.
This thread is now, therefore, "Solved".


----------

