Please help - problem performing calculation on fields from different tables (1 Viewer)

[choaspes]

I approach this knowligarchy as a wretched and humble supplicant, a beginner Access user who has hit that first brick wall that no amount of googling and tutorials appear to be able to help me over. It is probably, hopefully, a fairly simple question - but I'm stumped. I have attempted to translate my question into the idiom of "the small business" which seems ubiquitous in internet Access tutorials.

Say I have two tables: [customerorder] and [tariffs].

A record in [customerorder] comprises information about a customer and their current order, each record has a manually entered "order date" and a "tariff" that (with your help) should allow an Access query to calculate a "delivery date".

Each available "tariff" is listed in the [tariffs] table along with a number of other fields representing the various characteristics of that tariff - one of which is the field "delivery time". For some tariffs the delivery time is expressed in hours, some days and some months.

The master form is set up to allow users to manually enter data for records in [customerorder], with the exception of a "tariff" field which has a combo box listing the various records in [tariffs] as its control source.

How do I set up a query which calculates a "delivery date" and either stores this value in a new field in [customerorder] or at least links that value to the appropriate record in [customeroder]?

I have tried and failed in several ways. The one I was most hopeful about was expressing the "delivery time" field in [tariffs] as argument to a DateAdd expression e.g. (d, 27, [customerorder].[order date]) for tariff 1, (m, 6, [customerorder].[order date]) for tariff 2 etc... Then phrasing a query in terms of deliverydate: DateAdd[tariffs].[delivery time] - no dice.

It seems like it should be a very simply concept, but none of the primers on queries I've found have led to anything but error messages, perhaps a fundamental misunderstanding of relational databases and query structure is the problem. I would be very grateful to know the answer.

Thanks for your patience.

 

[Beetle]

Just to clarify, in your Tarriffs table you have a field that stores a (number? text?) value representing an interval of time that could be hours, days or months ,and you want to add this interval of time to the OrderDate field in customer orders based on which Tarriff is selected? Can you give a small representative example of your data and what you expect to see/happen?

 

[choaspes]

Thanks Beetle - you have it precisely correct.

Let's say the delivery time for tariff 1 is 14 days, and delivery time tariff 2 is 6 months.

If a customer has an "order date" of 1st Feb 2013, if I select "tariff 1" from my combo box I want the "delivery date" field to automatically read 15th Feb 2013. If I then change my mind and put him on "tariff 2" I want the "delivery date" field to automatically read 1st August 2013.

Ideally I'd have all these in dd/mm/yyyy but I thought I'd write it out to avoid any potential US/UK confusion in convention.

I have no preference as to how I go about expressing "delivery time" on the [tariffs] table, I started with number but couldn't make it work - then decided to try text and attempt to express the delivery time in the form of an argument for a DateAdd funciton performed by a query, but I couldn't get that working either - likely because I'm very weak on queries.

 

[Beetle]

You could store these interval values in two separate fields in your Tarriffs table, so in a text field you would have values for the interval type (h,d,m, etc. - i would store the values as they would be used in the DateAdd function) and in a number filed you would have the actual interval (6,14, whatever).

Now, in the Row Source query for the Tarriff combo box on your form add these additional fields. Set the Column Count of the combo box to 3 and the Column Widths to 1",0",0" (or whatever width works best for the first column, the point is just to hide the additional columns). Next, in an unbound text box on your form, place the following in the Control Source;

=DateAdd([YourCombo].[Column](1),[YourCombo].[Column](2),[OrderDate])

substituting the actual names of your combo box and order date fields of course. Also, if there are already other columns in the Row Source query of your combo box you may need to adjust the index values for the columns in the above example. It is a zero based index, so the first column is Column(0), the second is Column(1), etc.

 

[choaspes]

Thanks so much Beetle - that's really helpful.

I think I'm getting there, but the text box (edited to clarify - the box displaying my delivery date) displays "#Type!" irrespective of the value of the tariff and order date - presumably it's telling me that somewhere it's getting data in a format it doesn't like?

I've checked in the tables and my tariff interval field is set to text, my tariff number field set to number, and my order date field is set to date (I've tried specifying short date but that makes no difference). Any ideas?

Thanks again for being so helpful.

 

[Beetle]

What does the Control Source of that text box look like right now (copy and paste it here).

 

[choaspes]

=DateAdd([Combo154].[Column](1),[Combo154].[Column](2),[Order Date])

Edit - column 1 being interval and 2 being number

 

[Beetle]

And there are values in both the order date field and the combo box?

You might temporarily change the control source to;

=[Combo154].[Column](1)

just to see what value is being returned from that column, ditto for column 2.

 

[choaspes]

Eureka!!!!

Your suggested method of setting the text box to display all the individual components of the string has led me to diagnose the problem, or rather problems.

First problem was pretty embarrassing - I'd forgotten that ID was a non-displayed column in the combo box and so my column references were out by one. However, it still didn't work when I had them labelled up correctly in the control source: I started getting a #Func! error instead. But by taking the AddDate expression apart piece by piece and replacing bits with constants I eventually found the problem.

The control source needs to read:

=DateAdd([Combo].[column]("2"),[Combo].[column](3),[OrderDate])

You can't put the inverted commas in the interval field in the table or it doesn't work - you have to put them in the query!

Thanks again for taking the time to help me out, you steered me onto the right track and I'm delighted.