×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How create ranges for multiple dates associated with single record or AccountID in a table or query

How create ranges for multiple dates associated with single record or AccountID in a table or query

How create ranges for multiple dates associated with single record or AccountID in a table or query

(OP)
Is there a way to create ranges for multiple dates associated with single record or AccountID from a query/table so that one could later evaluate a date of another table or query to to say that AccountID 100000001004 at 7/18/2017 10:11:00 AM was AssignedTo 3632.

Three columns in the current table

AccountID AssgDate AssignedTO
100000001004 10/30/2017 12:10:00 PM -1
100000001004 11/7/2016 3:28:00 PM -1
100000001004 7/18/2017 10:05:00 AM 3632
100000001004 7/18/2017 10:13:00 AM 95590
100000001004 7/18/2017 11:29:00 AM -1
100000001004 7/25/2017 3:16:00 PM 3551
100000001004 9/9/2016 11:29:00 AM 3614
100000001104 11/7/2016 3:28:00 PM -1
100000001104 12/11/2017 3:09:00 PM -1
100000001104 7/18/2017 10:05:00 AM 3632
100000001104 7/18/2017 10:13:00 AM 95590
100000001104 7/18/2017 11:29:00 AM -1
100000001104 7/25/2017 3:16:00 PM 3551
100000001104 9/9/2016 11:34:00 AM 38189

I have had no luck in googling and securing an answer how to complete my quest, comments suggestions welcomed

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

Hi,

Your example has no Date/Time of 7/18/2017 10:11:00 AM for AccountID 100000001004.

How would anyone find it?

However, in your example there would be for AccountID 100000001004, Date/Time 7/18/2017 10:05:00 AM, AssignedTO 3632.

Don't know what you mean by, "ranges for multiple dates associated with single record". Any record has ONE Date/Time value.

However any specific AccountID may have multiple rows, like AccountID 100000001004, that has 7 rows.

So I don't understand what you need.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

It would be nice if you would:
1. Format your data so it would be easier to see what you have:

   AccountID      AssgDate           AssignedTO
 100000001004 10/30/2017 12:10:00 PM -1
 100000001004  11/7/2016  3:28:00 PM -1
 100000001004  7/18/2017 10:05:00 AM 3632
 100000001004  7/18/2017 10:13:00 AM 95590
 100000001004  7/18/2017 11:29:00 AM -1
 100000001004  7/25/2017  3:16:00 PM 3551
 100000001004   9/9/2016 11:29:00 AM 3614
 100000001104  11/7/2016  3:28:00 PM -1
 100000001104 12/11/2017  3:09:00 PM -1
 100000001104  7/18/2017 10:05:00 AM 3632
 100000001104  7/18/2017 10:13:00 AM 95590
 100000001104  7/18/2017 11:29:00 AM -1
 100000001104  7/25/2017  3:16:00 PM 3551
 100000001104   9/9/2016 11:34:00 AM 38189
 
and said...
2. "Above is what I have, below is what I would like to create:"

(Show an example of your desired outcome based on the data above)


---- Andy

There is a great need for a sarcasm font.

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

(OP)
Thanks for your comments Skip and Andy

Any AccountID can be assigned any number of times and there about 150k lines currently.I think I need to create ranges between the the each date and time and AccountID to help me to query whom an account was assigned at any given time.

I have two examples 100000001004 and 100000001104. The issue I'm wrestling with, there are other events that happen while the AccountID is assigned in which I would like to know to whom it was assigned when the event happened. Let's say a request or activity happened on AccountID 100000001004 at 7/18/2017 10:11:00 AM to whom was the account was assigned?

Three columns are present in the query, I cant seem paste as a table so I used <> separate

I would like be able to query select event's AccountIDs and select dates and times to establish to whom it was assigned when said events happened.... I hope this helps Rob

AccountID <> AssgDate <> AssignedTO
100000001004 <> 10/30/2017 12:10<> -1
100000001004 <> 11/7/2016 15:28 <> -1
100000001004 <> 7/18/2017 10:05 <> 3632
100000001004 <> 7/18/2017 10:13 <> 95590
100000001004 <> 7/18/2017 11:29 <> -1
100000001004 <> 7/25/2017 15:16 <> 3551
100000001004 <> 9/9/2016 11:29 <> 3614
100000001104 <> 11/7/2016 15:28 <> -1
100000001104 <> 12/11/2017 15:09<> -1
100000001104 <> 7/18/2017 10:05 <> 3632
100000001104 <> 7/18/2017 10:13 <> 95590
100000001104 <> 7/18/2017 11:29 <> -1
100000001104 <> 7/25/2017 15:16 <> 3551
100000001104 <> 9/9/2016 11:34 <> 38189




RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

Quote:

I think I need to create ranges between the the each date and time and AccountID to help me to query whom an account was assigned at any given time.

No! You don’t need anthing other than your table and the criteria values to find who was assigned at any particular time.

If you are doing a query, then...

CODE

Select AssignedTo
From YourTableName
Where AccountID=“100000001004” And AssgDate=#7/18/2017 10:11:00 AM#; 

It’s often the case, however, that an exact time is not available. In such cases you might use a criteria like this to show all times for a particular date...

CODE

Select AssignedTo, AssgDate
From YourTableName
Where AccountID=“100000001004” 
  And AssgDate=>#7/18/2017# And AssgDate<#7/19/2017#; 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

To format your data, use [PRE} ... [/PRE] TGML tags

"a request or activity happened on AccountID 100000001004 at 7/18/2017 10:11:00 AM to whom was the account was assigned?"

If your table is called Activity, you could do:

Select AssignedTO
From Activity
Where AccountID = 100000001004
And AssgDate <= #7/18/2017 10:11:00 AM#
Order By AssgDate DESC

And first record is what you want.


---- Andy

There is a great need for a sarcasm font.

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

(OP)
i'm needing to query about 25K AccountID's with various dates and times against these 150k Assigned dates and times. And wanting to know to whom it was assigned to for each date and time. Rob

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

Your question is still vague.

Do you have a list of 25,000 distinct AccountIDs for which you have one or more Date/Times to find the AssignedTo? Or what?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

What is the answer to the question of who is assigned to AccountID 100000001004 at 7/18/2017 10:11:00 AM?

CODE -->

100000001004	<>	7/18/2017 10:05	<>	3632
100000001004	<>	7/18/2017 10:13	<>	95590 

is the answer 3632 since the next record is not assigned until 10:13 to 95590?

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

What happens in vagueness, stays in vagueness!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

(OP)
Yes and likely to grow, both are.

One table/query that has the accounts assigned to various AssignedTO's at various dates and times in one table/query (150K) Lets call it "All_Assgnd_1"

In another table/query that have AccountID's with an event with various Dates and times (25) lets call it "All_Act1_Events"

In which I need to know what was the AssignedTO based on "All_Assgnd_1" had it at the time the "All_Act1_Events" event(s)happened

Rob

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

(OP)
MajP

Yes exactly, 3632, since the next record is not assigned until 10:13 to 95590. Rob

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

CODE

Select aa1.AssignedTo, aa1.AssgDate, aa1.AccountID
From All_Assgnd_1 aa1, All_Act_Events aae
Where aa1.AccountID=Aae.AccountID
  And aa1.AssgDate=Aae.aae.AssgDate 

You might also post an example of All_Assgnd_1 and All_Act_Events with the results that you expect from your example.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

I may be interpreting this differently. But If the range is that time between each record then and the assigned to of the range is the starting assignedto

CODE -->

SELECT 
 A.AcctID, 
 A.AssignedTo, 
 A.AssgnDate AS StartRange, 
(Select min (b.AssgnDate) as EndRange from Acctinfo as B where A.acctID = b.acctID and B.assgnDate > a.assgnDate) AS EndRange
FROM 
 acctinfo AS A
ORDER BY 
 A.AcctID, A.AssgnDate; 

This give me the results

CODE -->

AcctID	AssignedTo	StartRange	EndRange
100000001004	3614	9/9/2016 11:29	11/7/2016 3:28:00 PM
100000001004	-1	11/7/2016 15:28	7/18/2017 10:05:00 AM
100000001004	3632	7/18/2017 10:05	7/18/2017 10:13:00 AM
100000001004	95590	7/18/2017 10:13	7/18/2017 11:29:00 AM
100000001004	-1	7/18/2017 11:29	7/25/2017 3:16:00 PM
100000001004	3551	7/25/2017 15:16	10/30/2017 12:10:00 PM
100000001004	-1	10/30/2017 12:10	
100000001104	38189	9/9/2016 11:34	11/7/2016 3:28:00 PM
100000001104	-1	11/7/2016 15:28	7/18/2017 10:05:00 AM
100000001104	3632	7/18/2017 10:05	7/18/2017 10:13:00 AM
100000001104	95590	7/18/2017 10:13	7/18/2017 11:29:00 AM
100000001104	-1	7/18/2017 11:29	7/25/2017 3:16:00 PM
100000001104	3551	7/25/2017 15:16	12/11/2017 3:09:00 PM
100000001104	-1	12/11/2017 15:09	 

So for 004 then 3632 owns any records for times between 10:05 and 10:13.
So you would use the above query call it qryRanges with your other table that has accountIDs and times. Join the other table by accountID and the where clause would be "where someTimeField between qryRanges.StartRange and qryRanges.EndRange. This would tell you that 004 10:11 is assigned to 3632. I do not know how to handle something that happens at the end of the range. For example for 004 the last record is 10/30 12:10. If you have something in your other table after 12:10 you would have to write a different query to handle that.

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

So if you had info in another table like this called acctinfo2

CODE -->

AcctID	AssgnDate
100000001004	7/18/2017 10:11
100000001004	7/26/2017 10:29
100000001004	10/31/2017 10:30 

You can figure out the assigned to by (qryAssigned)

CODE -->

SELECT qryRanges.AcctID, qryRanges.AssignedTo, qryRanges.StartRange, qryRanges.EndRange, acctInfo2.AssgnDate AS SearchDate
FROM qryRanges INNER JOIN acctInfo2 ON qryRanges.AcctID = acctInfo2.AcctID
WHERE (((acctInfo2.AssgnDate) Between [qryRanges].[StartRange] And [qryRanges].[EndRange])); 

CODE -->

AcctID	AssignedTo	StartRange	EndRange	SearchDate
100000001004	3632	7/18/2017 10:05	7/18/2017 10:13:00 AM	7/18/2017 10:11
100000001004	3551	7/25/2017 15:16	10/30/2017 12:10:00 PM	7/26/2017 10:29 

notice how the last record is not found that is because there is a null for endrange after 10/30 12:10 for account 004. I would write a separate query to pick up these cases and union the two resulting queries.

So that query would be (qryAssignedEnd):

CODE -->

SELECT qryRanges.AcctID, qryRanges.AssignedTo, qryRanges.StartRange, qryRanges.EndRange, acctInfo2.AssgnDate AS SearchDate
FROM qryRanges INNER JOIN acctInfo2 ON qryRanges.AcctID = acctInfo2.AcctID
WHERE (((qryRanges.EndRange) Is Null) AND ((acctInfo2.AssgnDate)>[qryRanges].[StartRange])); 

This finds the missing record

CODE -->

AcctID	AssignedTo	StartRange	EndRange	SearchDate
100000001004	-1	10/30/2017 12:10		10/31/2017 10:30 

There may be a way to build the where statement to do this in one query.

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

(OP)
MaJP

At the end of the range it should be treated as through today or now. I think you got something here I will work with this over the next couple days. I truly appreciate your and Skip's efforts and this site. Rob

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

I believe that second query would work for the end of the range. So your final solution is simply
select * from qryAssigned
Union
Select * from qryAssignedEnd

where qryAssigned and qryAssignedEnd are the queries

CODE

SELECT qryRanges.AcctID, qryRanges.AssignedTo, qryRanges.StartRange, qryRanges.EndRange, acctInfo2.AssgnDate AS SearchDate
FROM qryRanges INNER JOIN acctInfo2 ON qryRanges.AcctID = acctInfo2.AcctID
WHERE (((acctInfo2.AssgnDate) Between [qryRanges].[StartRange] And [qryRanges].[EndRange])); 

SELECT qryRanges.AcctID, qryRanges.AssignedTo, qryRanges.StartRange, qryRanges.EndRange, acctInfo2.AssgnDate AS SearchDate
FROM qryRanges INNER JOIN acctInfo2 ON qryRanges.AcctID = acctInfo2.AcctID
WHERE (((qryRanges.EndRange) Is Null) AND ((acctInfo2.AssgnDate)>[qryRanges].[StartRange])); 

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

(OP)
MaJP

Darn, I cannot get this code to complete, its shows the first 30 rows but if I try to goto last record or try to make a table, it hangs

SELECT
A.AcctID,
A.AssignedTo,
A.AssgnDate AS StartRange,
(Select min (b.AssgnDate) as EndRange from Acctinfo as B where A.acctID = b.acctID and B.assgnDate > a.assgnDate) AS EndRange
FROM
acctinfo AS A
ORDER BY
A.AcctID, A.AssgnDate;

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

Subqueries in access tend to run slow. Without seeing this, I cannot help. However, you could do this in code and make a table of ranges.
Build table TblRanges
AcctID
StartRange
EndRange
AssignedTo

My AcctID is a text and AssignedTo is a long, so you will have to modify the parenthesis if that differs

Loop your table and populate the ranges.

CODE -->

Public Sub FillRanges()
  Dim RS_Source As DAO.Recordset
  Dim StrSql As String
  Dim NextAcct As String 'modify if this is a number
  Dim CurrentAcct As String
  Dim StartRange As Date
  Dim EndRange As Date
  Dim AssignedTo As Long
  'Need to make sure in proper order of Account and assign date
  StrSql = "SELECT acctinfo.AcctID, acctinfo.AssgnDate, acctinfo.AssignedTo FROM acctinfo ORDER BY acctinfo.AcctID, acctinfo.AssgnDate"
  'open forward only for performance
  Set RS_Source = CurrentDb.OpenRecordset(StrSql, dbOpenForwardOnly)

  Do While Not RS_Source.EOF
    CurrentAcct = RS_Source!acctID
    StartRange = RS_Source!assgnDate
    AssignedTo = RS_Source!AssignedTo
    'move to next record
    RS_Source.MoveNext
    'if no more records
    If RS_Source.EOF Then
      EndRange = Now
      NextAcct = CurrentAcct
    Else
       'an account change
       NextAcct = RS_Source!acctID
       If NextAcct <> CurrentAcct Then
        EndRange = Now
       Else
        EndRange = RS_Source!assgnDate
       End If
    End If
    StrSql = "Insert into TblRanges (AcctID,StartRange,EndRange,AssignedTo) values ('" & CurrentAcct & "',"
    StrSql = StrSql & SQLDate(StartRange) & "," & SQLDate(EndRange) & "," & AssignedTo & ")"
    Debug.Print StrSql
    CurrentDb.Execute StrSql
  Loop
End Sub
Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function 

The insert query would look like this

CODE -->

Insert into TblRanges (AcctID,StartRange,EndRange,AssignedTo) values ('100000001004',#07/18/2017 11:29:00#,#07/25/2017 15:16:00#,-1) 
And my final table looks like

CODE -->


ID	AcctID	StartRange	EndRange	AssignedTo
1	100000001004	9/9/2016 11:29:00 AM	11/7/2016 3:28:00 PM	3614
2	100000001004	11/7/2016 3:28:00 PM	7/18/2017 10:05:00 AM	-1
3	100000001004	7/18/2017 10:05:00 AM	7/18/2017 10:13:00 AM	3632
4	100000001004	7/18/2017 10:13:00 AM	7/18/2017 11:29:00 AM	95590
5	100000001004	7/18/2017 11:29:00 AM	7/25/2017 3:16:00 PM	-1
6	100000001004	7/25/2017 3:16:00 PM	10/30/2017 12:10:00 PM	3551
7	100000001004	10/30/2017 12:10:00 PM	1/11/2018 12:32:20 PM	-1
8	100000001104	9/9/2016 11:34:00 AM	11/7/2016 3:28:00 PM	38189
9	100000001104	11/7/2016 3:28:00 PM	7/18/2017 10:05:00 AM	-1
10	100000001104	7/18/2017 10:05:00 AM	7/18/2017 10:13:00 AM	3632
11	100000001104	7/18/2017 10:13:00 AM	7/18/2017 11:29:00 AM	95590
12	100000001104	7/18/2017 11:29:00 AM	7/25/2017 3:16:00 PM	-1
13	100000001104	7/25/2017 3:16:00 PM	12/11/2017 3:09:00 PM	3551
14	100000001104	12/11/2017 3:09:00 PM	1/11/2018 12:32:20 PM	-1
 

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

(OP)
Stops at "Set RS_Source = CurrentDb.OpenRecordset(StrSql, dbOpenForwardOnly)" missing a table?

Public Sub FillRanges()
Dim RS_Source As DAO.Recordset
Dim StrSql As String
Dim NextAcct As String 'modify if this is a number
Dim CurrentAcct As String
Dim StartRange As Date
Dim EndRange As Date
Dim AssignedTo As Long
'Need to make sure in proper order of Account and assign date
StrSql = "SELECT All_Assgnd_1.AccountID, All_Assgnd_1.AssgDate, All_Assgnd_1.AssignedTO FROM acctinfo ORDER BY All_Assgnd_1.AccountID, All_Assgnd_1.AssgDate"
'open forward only for performance
Set RS_Source = CurrentDb.OpenRecordset(StrSql, dbOpenForwardOnly)

Do While Not RS_Source.EOF
CurrentAcct = RS_Source!AccountID
StartRange = RS_Source!AssgDate
AssignedTo = RS_Source!AssignedTo
'move to next record
RS_Source.MoveNext
'if no more records
If RS_Source.EOF Then
EndRange = Now
NextAcct = CurrentAcct
Else
'an account change
NextAcct = RS_Source!AcctID
If NextAcct <> CurrentAcct Then
EndRange = Now
Else
EndRange = RS_Source!assgnDate
End If
End If
StrSql = "Insert into TblRanges (AcctID,StartRange,EndRange,AssignedTo) values ('" & CurrentAcct & "',"
StrSql = StrSql & SQLDate(StartRange) & "," & SQLDate(EndRange) & "," & AssignedTo & ")"
Debug.Print StrSql
CurrentDb.Execute StrSql
Loop
End Sub

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

I assume you mean

CODE -->

SELECT AccountID, AssgDate, AssignedTO, FROM All_Assgnd_1 ORDER BY AccountID, AssgDate 

To make it simpler build the above query and save it as qryAssgDates. make sure it runs. Then change it to simply
StrSql = "qryAssgDates"

also limit qryAssgDates to a few records with a where statement so you do not try to test on 150k records.

Another way to test it would be to add this line of code

CODE -->

CurrentDb.Execute StrSql
exit sub
Loop
End Sub 
That way you can test if it creates one record. If it does, then you can remove the "exit sub" to allow it to loop

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

(OP)
Thanks so much MajP

Okay so I did create the suggested query and limited it to 100 records and with the code pasted at the end of this post, it returns 16 records but the first time it was and the last time assigned not each time it was assigned ...Rob

For example for these AccountId's
AccountID	AssgDate	        AssignedTO
100000000904	11/7/2016 3:28:00 PM	-1
100000000904	4/20/2017 3:14:00 PM	3551
100000000904	5/16/2017 1:06:00 PM	3593
100000000904	7/18/2017 10:05:00 AM	3632
100000000904	7/18/2017 10:13:00 AM	95590
100000000904	9/5/2017 4:24:00 PM	-1
100000000904	9/9/2016 11:31:00 AM	36678
100000001004	10/30/2017 12:10:00 PM	-1
100000001004	11/7/2016 3:28:00 PM	-1
100000001004	7/18/2017 10:05:00 AM	3632
100000001004	7/18/2017 10:13:00 AM	95590
100000001004	7/18/2017 11:29:00 AM	-1
100000001004	7/25/2017 3:16:00 PM	3551
100000001004	9/9/2016 11:29:00 AM	3614
100000001104	11/7/2016 3:28:00 PM	-1
100000001104	12/11/2017 3:09:00 PM	-1
100000001104	7/18/2017 10:05:00 AM	3632
100000001104	7/18/2017 10:13:00 AM	95590
100000001104	7/18/2017 11:29:00 AM	-1
100000001104	7/25/2017 3:16:00 PM	3551
 

But only returns 3 records with the first AssignedTo and the first StartRange and the last EndRange
 
AccountID	StartRange	        EndRange	        AssignedTo
100000000904	11/7/2016 3:28:00 PM	4/20/2017 3:14:00 PM	-1
100000001004	10/30/2017 12:10:00 PM	11/7/2016 3:28:00 PM	-1
100000001104	11/7/2016 3:28:00 PM	12/11/2017 3:09:00 PM	-1
 

Code
Public Sub FillRanges()
  Dim RS_Source As DAO.Recordset
  Dim StrSql As String
  Dim NextAcct As String 'modify if this is a number
  Dim CurrentAcct As String
  Dim StartRange As Date
  Dim EndRange As Date
  Dim AssignedTo As Long
  'Need to make sure in proper order of Account and assign date
  StrSql = "qryAssgDates"
  'open forward only for performance
  Set RS_Source = CurrentDb.OpenRecordset(StrSql, dbOpenForwardOnly)

  Do While Not RS_Source.EOF
    CurrentAcct = RS_Source!AccountID
    StartRange = RS_Source!AssgDate
    AssignedTo = RS_Source!AssignedTo
    'move to next record
    RS_Source.MoveNext
    'if no more records
    If RS_Source.EOF Then
      EndRange = Now
      NextAcct = CurrentAcct
    Else
       'an account change
       NextAcct = RS_Source!AccountID
       If NextAcct <> CurrentAcct Then
        EndRange = Now
       Else
        EndRange = RS_Source!AssgDate
       End If
    End If
    StrSql = "Insert into TblRanges (AccountID,StartRange,EndRange,AssignedTo) values ('" & CurrentAcct & "',"
    StrSql = StrSql & SQLDate(StartRange) & "," & SQLDate(EndRange) & "," & AssignedTo & ")"
    Debug.Print StrSql
    CurrentDb.Execute StrSql
  Loop
End Sub
 



RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

You set AccountID as a primary key or indexed it not to allow duplicates, therefore it has to be unique. Every other record for that accountID fails after the first one. Unlike running an update query from the user interface, running from code will not provide you an error if the update does not go through because of violating a constraint.

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

(OP)
I did? How ? and can I alter that?

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

You built a table TblRanges correct?
AcctID
StartRange
EndRange
AssignedTo

Is AcctID a primary key? Does it allow duplicates?

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

(OP)
I see, ok I remedied the primary key issue thanks now I get an error

Run-Time error'94':
Invalid use of Null

Which happens at "AssignedTo = RS_Source!AssignedT" of this snippet of code

 Do While Not RS_Source.EOF
    CurrentAcct = RS_Source!AccountID
    StartRange = RS_Source!AssgDate
    AssignedTo = RS_Source!AssignedTo
    'move to next record
    RS_Source.MoveNext
    'if no more records
    If RS_Source.EOF Then
      EndRange = Now
      NextAcct = CurrentAcct
 

It did get to 38,203 records prior to stopping thought!



RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

you may need to add some error checking. It appears you have records without AssignedTo. So what do you want to do in that case?

CODE -->

AccountID	AssgDate	        AssignedTO
100000000904	11/7/2016 3:28:00 PM	-1
100000000904	4/20/2017 3:14:00 PM 

I would find these first and fix them. Query your records for where AssignedTO is Null. Or your code could assign a bogus ID
AssignedTo = NZ(RS_Source!AssignedTo,9999)

This code will also bomb if your AccountID or AssgDate is null.

RE: How create ranges for multiple dates associated with single record or AccountID in a table or query

(OP)
Majp

Thank so very much for your help! Testing for a bit but looking good...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close