Data from two tables on one form?

[10 Gauge]

This one should be pretty simple but it's giving me a headache.



I have 2 primary forms for different states, one for Virginia and one for Washington DC. By default the Virginia form auto executes when the database is open because our company operates 90% in Virginia and we have an occasional job in DC.

It's a ticket tracking system which is designed to alert our estimators when a ticket needs to be updated. I would like to have an alert (just a text box that says "DC Updates Due" in bold red text) on the form if any updates are due on the DC form/table.

Virginia uses it's own table for tickets and DC uses it's own table for tickets.

Can this be done?

Edit: Can this line of code be modified to look in a different table: =IIf(Int([Update Date])-Int(Date())<2,"DC Updates Due")

[pbaldy]

Are the fields in the two tables basically the same? Normally, you would not have multiple tables like that. You'd have the data in one table, with a field to indicate which state. You'd have one form, which you could filter to display either state, or both.

[10 Gauge]

That's the way I was going to have it set up but with the different states come different regulations and different systems for the tickets so the tables aren't the same, they both use a completely different set of fields. I'm sure there would have still been a way to do it like that but I needed to get her done lickity-split with my feeble Access knowledge.

[pbaldy]

I would think you could use a DCount() to check the values in the other table. Syntax here:

DLookup Usage Samples

[10 Gauge]

Ok this looks like it will work... I am trying to figure out how I would use this in conjunction with the code from the first post.

[pbaldy]

If you get stuck, post your effort and the required logic.

[10 Gauge]

I don't even know where to start honestly... I don't know the proper structure to string the two together.

=DLookup("[Update Date]" , "[Tasks - DC]" , "Criteria=Date()<2" , "DC Updates Due")

???

I want the command to check the [Update Date] field on the [Tasks - DC] table and if any dates that are less than 2 days away from the current date to display the message "DC Updates Due" in a text box on the form.

[pbaldy]

Try

=IIf(DCount("*" , "[Tasks - DC]" , "[Update Date] < Date() + 2) > 0, "DC Updates Due","")

[10 Gauge]

I get the following message:

The expression you entered contains invalid syntax. You may have entered an operand without an operator.

[pbaldy]

Oops, try

=IIf(DCount("*" , "[Tasks - DC]" , "[Update Date] < Date() + 2") > 0, "DC Updates Due","")

or

=IIf(DCount("*" , "[Tasks - DC]" , "[Update Date] < #" & Date() + 2 & "#") > 0, "DC Updates Due","")

[10 Gauge]

Hot diggity damn it worked! Much thanks pbaldy!

[pbaldy]

Ah good, glad we got it sorted out. I'll mark the thread solved unless you have further problems with this.