Between Two Dates - Different Table

[CodenameAter]

Hello,



First time post and newbie to Access.

I have a number of IDs as well as a start and end dates and times for that ID. On another table I have raw data with times and dates. On the second table, I want to list the ID beside each raw data point if the raw data points date and time falls between any of the IDs present. However, the dates and times are in two separate fields as they are imported from our data collection tool comma-delimited. I can't figure out the expression to solve this. However in excel, I would write something like:

if(and(IDStartDate<RawDataDate<IDEndDate , IDStartTime<RawDataTime<IDEndTime), ID, "No ID")

However, when I write the following Iif Statement, it doesn't work:

IIf([Block Dates]![Block Start Date]<[Date]<[Block Dates]![Block End Date],[Block Dates]![BlockID],"No ID")

Even this doesn't work:

IIf(Date( [Block Dates]![Block Start Date]) < Date([Date]) < Date([Block Dates]![Block End Time]), [Block Dates]![BlockID], "NO ID")

In addition, this only deals with the Date values and I also need to account for Time. Time is also screwed up because when it enters into the system it picks an random date to go along with it, so I just want it pointing to the time value.

Let me know. Thank you.

[CodenameAter]

My original question above might be confusing so this might simplify things. I have created two queries that combine a bunch of information from different tables:



In the Banded-Scrub is all the data collection from each one of my stations. The Merged-BlockInfo pulls all the data on the experiments we are running together. This query lists the BlockID (experiment name), the start time & date, end time & date, the LocID (same as LocID in the Banded-Scrub) and the LocType.

All I want is the Banded-Scrub to look the same except add fields for BlockID and LocType values. These fields should be populated based on their dates relative to the start & end dates of the Merged-BlockInfo.

I hope this helps

[June7]

Date() and Now() are functions without an argument. So your last expression definitely won't work. CDate() is conversion function.

Looks like you use multi-value fields. I NEVER use them.

Date and Time parts are best saved together into 1 field.

Try:

SELECT Banded-Scrub.*, Merge-BlockInfo.BlockID.Value, Merge-BlockInfo.LockType FROM Banded-Scrub, Merge-BlockInfo WHERE [Date] & " " & Format(Start_Time, "hh:nn:ss") >= [Block Start Date] & " " & Format([Block Start Time], "hh:nn:ss") AND [Date] & " " & Format(End_Time, "hh:nn:ss") <= [Block End Date] & " " & Format([Block End Time], "hh:nn:ss");


Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Also should not use reserved words as names. Date is a reserved word.

[CodenameAter]

Ya, I don't like that Time and Date are separate either, that's how the data feeds from the system. I can combine them in the future if needed.

I can also change the names of any of the fields, that's not a problem at all

Thank you, I will give it a try this afternoon and get back to you

[CodenameAter]

I have another question, somewhat related to this.

Under the Merged Block Info, I am having an issue of identifying unique values.

In short:
BlockIDs are experiment names
Day #'s are which day of the experiment we are on: Day 1, Day 2 or Day 3

For each day I have different setups for different Locations. For example, Locations 1-4 all stay the same but locations 5-8 change. In another table, I select the Experiment Name, The Day Number, The Location ID and then assign a Location Type. However, when I merge the data together, it treats any line with the same Day # as a unique line. That is, all my experiments have a Day 1, therefore any Location Type that shares the same record as Day 1, gets a unique value. Experiments that don't even have a certain Location Type, still get a unique line up in the query with that Location Type, simply because I reuse Day 1 for many different experiments.

I hope this makes sense. I am clearly making some fatal Access mistakes when setting up the database but I have no idea how to correct this.

[June7]

What do you mean by 'merge'?

Post code or query or db for analysis.

[CodenameAter]

I figured it out. So basically, access likes having one unique value per table. If your table doesn't have a field with any unique values and it's not a raw data input, then it gets upset. Therefore, each of my experiments trial and day require a unique value in which I reference. I appreciate this probably makes no sense without showing you the database

[CodenameAter]

So I took your advice and updated all my tables and fields so that they don't create issues. However, when I tried to update your code with the relative fields, I kept getting error messages. Here is a list of the update tables, is there something fundamentally different that updating the code with the correct names would not still work?



Just to recap. I am trying to show all the data in Banded_Scrub Query as well as Experiment_Name, BlockID and LocType in one query. Using the date & time in the Banded_Scrub, I am hoping to have these (Experiment_Name, BlockID and LocType) added in respective to their date & time in Merge_BlockInfo.

I can combine start dates and times if that would make things easier, I figured out how to do that quiet easy in my data upload. I eventually want to understand what is going on so that I can manipulate the code myself. If combining times will do that, then I can do that first

[June7]

Date/Time parts in one field is usually best because it is easy to extract parts from the whole using date/time functions. This is the opposite of text data which is usually easier to concatenate than take apart.

However, the separate fields can be dealt with.

Post attempted code for analysis.

Post exact error messages.

[CodenameAter]

Okay, I will combine the date & time into one and repost. I won't bother posting the SQL code since the values will change with the combined values

[CodenameAter]

I have updated the tables. I have also changed the names for most of the table values to reflect your suggestions above. Here is a screenshot of the tables:



Here is the expression I attempted for just part of what I need. Block would be a new field in the query:

Block: IIf([Merge_BlockInfo]![Start]<=[Arrive]<=Format([Start],"dd/mm/yyyy") And TimeSerial(23,59,59),[Merge_BlockInfo]![BlockID],"NO ID")

However, this failed to work. Maybe you can help me where I went wrong. What I wanted was this:

--> If [Arrive] is greater than [Start] but less than [Start]*Date Only and 23:59:59 (i.e. midnight), return the [BlockID] for that.
More simply put, the subjects arrival time should fall between the start of the BlockID and midnight.

Here comes the tricky part I have no idea how to do, but in words I am looking for the following:

-->If [Arrive] is between 3am to 7am after [BlockID], return [BlockID] as well as a"-L" behind it. (For example, a block normal reads "Prog3.1", I would just want it to say "Prog3.1-L")
More simply put, if the arrival happens between 3am to 7am the day after any block [Start], give me the [BlockID] from the day before so I know it is associated with that block but has a "-L" so that I know it corresponds to the next day.

In addition, I need another field very similar but for [LocType]. Every [BlockID], I've listed all the [LocIDs] and written in their corresponding [LocType]. I need that [LocType] returned. Basically:

--> Based on [BlockID] and [LocID], return corresponding [LocType].


Again, I hope this makes sense and I appreciate you looking into this. I know zero SQL, so I tried to write it in the expression builder, as I am more comfortable with that. However, if SQL is the only way to do this, then so be it

Thank you so much!!

[June7]

Try:

Block: IIf([Merge_BlockInfo]![Start]<=[Arrive] AND [Arrive]<=Format([Start],"dd/mm/yyyy") & " 11:59:59 PM", [Merge_BlockInfo]![BlockID], "NO ID")

Or

Block: IIf([Arrive] BETWEEN [Merge_BlockInfo]![Start] AND Format([Start],"dd/mm/yyyy") & " 11:59:59 PM", [Merge_BlockInfo]![BlockID], "NO ID")

[CodenameAter]

So it sort of worked except that it returned all NO ID.

Would this work better if I linked the original tables instead of using the Merged_BlockInfo query?

[June7]

Can try but shouldn't make any difference.

If you want to provide db for analysis, follow instructions at bottom of my post.

[CodenameAter]

Here you go. I cut out everything I could and limited it to four days of data... Original file is over 60 MB but this sample should suffice.

Thanks again for looking at this