Populating Blanks based on prior value

Queries work on set theory and it is unlikely that you could get one to do this update since the update is dependent on record position.  If you can sort the data into the sequence you displayed, you can write an update loop to read each record and if users is not zero, save it.  If users is zero, fill it with the last saved value and update the record.  You will need to deal with first and last records separately.

In a standard database you would not be doing this manually anyway.

If you know that a client will pay the same amount each month of a given quarter, then this could be a function, ...then the function could generate the dates and the amounts.
Unfortunately any function (or system)  to do this could grow to be quite complex (depending on your needs), ...and, will, most likely, tax your system if your datasets are large (function may have to calculate the "previous" month's amount for each row)

In other words, ...Access does not have an quick and easy "AutioFill" utility like Excel does.
But there may be an expert here willing to create (a simple) one for you...?

to me, ...It might be simpler (and faster) just to use the keyboard shortcut: Ctrl+'
or: Ctrl+Shift+"
This keyboard shortcut will copy the entry above
This shortcut works in Excel as well as in Access

If you still need to see what a code solution might look like , ...here  the code and a sample db:

Dim lngCustID As Long
Dim currAmount As Currency
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT tblBilling.InvoiceID, tblBilling.custID, tblBilling.[Invoice Date], tblBilling.InvoiceAmt FROM tblBilling ORDER BY tblBilling.custID, tblBilling.[Invoice Date];")
rst.MoveFirst
currAmount = rst!InvoiceAmt
lngCustID = rst!custID
rst.MoveNext


Do Until rst.EOF
    If rst!custID = lngCustID And rst!InvoiceAmt = 0 Then
        rst.Edit
        rst!InvoiceAmt = currAmount
        rst.Update
    ElseIf rst!custID = lngCustID And rst!InvoiceAmt > 0 Then
        currAmount = rst!InvoiceAmt
    ElseIf rst!custID <> lngCustID And rst!InvoiceAmt > 0 Then
        lngCustID = rst!custID
        currAmount = rst!InvoiceAmt
        rst.Edit
        rst!InvoiceAmt = currAmount
        rst.Update
    End If
    rst.MoveNext
Loop

MsgBox "Done"

rst.Close
Set rst = Nothing

Select allOpen in new window

JeffCoachman
Access--EEQ28735749-AutoSum.accdb

To get my code above to work in your db:
First, make a copy of your original table
Then you will have to change my object names to the corresponding names in your db.

Thanks all for your comments and feedback as well as knowlege sharing.

Jeffrey - I am going to try your solution and will respond in a bit.

ok

Jeffrey,

I downloaded your example db, thanks.

Am not sure how to run the function or how it works?  there are two tables, tblBilling and tblBillingSAF.  Looks like one has all values in InvAmt, and the other has only Qtrly payments, which is how my data (somewhat) looks like.  The Invamt, is actually Users in my db, but I can fix that.  So, Invamt is always populated, on a monthly basis, but the total of users only shows up quarterly -- I need to pull that number into each month.  So, if blank, take the previous number?  Not sure how to use your tool.

*Download the sample again
Open the database
*Close the opening Form, ...Do Not click the button*
First, examine the tables
Notice that both tables are exactly the same.
I only created the "SAF" version as a backup.
(You can delete the SAF table if you like...)

Note that tblBilling has populated data similar to what you posted.
(a starting value only, ...for each quarter)

Now reopen the form and click the button
When done, close the form and look at tblBilling again...
Note that now the first quarterly values are populated for the subsequent months for that quarter...

Ooooh....!  Yeah, I was thinking maybe that's what happened, that I updated the table before looking, but couldn't be sure, so I went into the table and deleted some data in the months between qtrs and ran the form again, but it kept throwing an error.

Will check it out again.

Question, does the code always check for a specific number of months, or does it simply look for the last value, then populate?  Meaning:

Jan, Feb, empty, Mar value goes into Jan Feb
Apr, May, June, maybe Jul, emply, Sept value, goes into "only" Apr, May, june, Jul.

Will your solution work for me?

Thank you!!  :)

Hi, just got a moment to check out your db and related code.  I don't have an invoice ID for this query (which I've dumped into a new table).

Trying to edit your code to work on a test db but got stuck on whether I need an invoice ID?  There are invoice IDs, however, the query that I need to update doesn't include it.  Guess I can make a new ID...just wondering here?

Jeffery,

I updated your code, attached below.  In addition to changing table name, and field name, I declared the field what would be updated to be LONG and not CURRENCY -- maybe this is where I messed up.  The user totals (of which I am trying to update) is simply a number, no more than 1,000.

the error I am getting is:
Run-time error '94':
Invalid use of Null

============
Private Sub Command0_Click()
Dim lngRevSFCRMID As Long
Dim lngUsers As Long
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT tRevBillTtls.NewInvId, tRevBillTtls.RevSFCRMID, tRevBillTtls.[SortRevDate], tRevBillTtls.AfterTtlUsers FROM tRevBillTtls ORDER BY tRevBillTtls.RevSFCRMID, tRevBillTtls.[SortRevDate];")
rst.MoveFirst
lngUsers = rst!AfterTtlUsers
lngRevSFCRMID = rst!RevSFCRMID
rst.MoveNext


Do Until rst.EOF
    If rst!RevSFCRMID = lngRevSFCRMID And rst!AfterTtlUsers = 0 Then
        rst.Edit
        rst!AfterTtlUsers = lngUsers
        rst.Update
    ElseIf rst!RevSFCRMID = lngRevSFCRMID And rst!AfterTtlUsers > 0 Then
        lngUsers = rst!AfterTtlUsers
    ElseIf rst!RevSFCRMID <> lngRevSFCRMID And rst!AfterTtlUsers > 0 Then
        lngRevSFCRMID = rst!RevSFCRMID
        lngUsers = rst!AfterTtlUsers
        rst.Edit
        rst!AfterTtlUsers = lngUsers
        rst.Update
    End If
    rst.MoveNext
Loop

MsgBox "Done"

rst.Close
Set rst = Nothing

End Sub

Jeffery, I searched a solution for the error 94 and believe it has to do with null values. I replaced all blanks in the User field with zero (0) and reran your code.

I am now getting another error:
Run-time error '13':
Type mismatch

using the debugger, it appears that its this line:
lngRevSFCRMIN = rst!RevSFCRMID (this is my customer ID)

This field is set to "short text' and looking at your table, your custID is also short text -- so not sure what to do now.

OK,
Give me a day or so, ...I had minor surgery yesterday and I am just now catching up on my emails...

...or post a simplified version of your database for me to examine...

In any event, Type MisMatch error means the datatypes do not match.

In Access the datatypes must match *exactly*, to be syntactically correct.

So however "RevSFCRMID" is set as the *FieldSize* in the table, ...you must declare the variable with the same type.

Thanks Jeffrey -- hope you are doing better, feeling well.

I will relook at the datatype and also post a sample db if needed.

thanks,
Alicia

Jeffrey,

Okay, I changed my customerID to be declared as string, and it seemed to fix my errors. (Yay!!).  Your code is working beautifully, however, one issue and am hoping the code can be tweaked.  It appears that if there are blanks before the first value of a "user" (this is currency amount in your table, but is User counts in my db) the value stays null (or zero) and it only updates records from a go forward position.  So, if the record is blank, the value should be the next value, popluated until a new value is found.  In the ABC Company example attached, you will see that the first value didn't appear until May 2015, so all months before should also have the value of 1.  The code did populate the go forward months.  In the DEF Company, the code did a great job capturing the change of users -- which is perfect!!

Here is the code I've edited to work on my test db:

================================

Private Sub Command0_Click()
Dim strRevSFCRMID As String
Dim lngUsers As Long
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT tRevBillTtls.NewInvId, tRevBillTtls.RevSFCRMID, tRevBillTtls.[SortRevDate], tRevBillTtls.AfterTtlUsers FROM tRevBillTtls ORDER BY tRevBillTtls.RevSFCRMID, tRevBillTtls.[SortRevDate];")
rst.MoveFirst
lngUsers = rst!AfterTtlUsers
strRevSFCRMID = rst!RevSFCRMID
rst.MoveNext


Do Until rst.EOF
    If rst!RevSFCRMID = strRevSFCRMID And rst!AfterTtlUsers = 0 Then
        rst.Edit
        rst!AfterTtlUsers = lngUsers
        rst.Update
    ElseIf rst!RevSFCRMID = strRevSFCRMID And rst!AfterTtlUsers > 0 Then
        lngUsers = rst!AfterTtlUsers
    ElseIf rst!RevSFCRMID <> strRevSFCRMID And rst!AfterTtlUsers > 0 Then
        strRevSFCRMID = rst!RevSFCRMID
        lngUsers = rst!AfterTtlUsers
        rst.Edit
        rst!AfterTtlUsers = lngUsers
        rst.Update
    End If
    rst.MoveNext
Loop

MsgBox "Done"

rst.Close
Set rst = Nothing

End Sub
MissingValusPicture.PNG

Jeff,

Appologies for the delay as I was on vacation.

Thanks for your very detailed explanation and yes, it does make sense to me.  The image was in Excel as I exported the data into Excel from Access to highlight the missing values.

The db I am trying to create will only be used to link up two data sets from two different systems and then further extract required information from a monthly basis.  One side of the data breaks out revenue on a monthly basis, however, the other side of the data, (billing) bills only when needed, could be monthly, or quarterly or annual -- and that is the issue.  I need a data point in the billing system that isn't in the revenue system and thought linking up data sets might do the trick.

Your solution has gotten me a lot farther than trying to manually update data in Excel, so that you!  It is helpful, nonetheless.

Hope you are feeling better and fully recovered.

Great communication with added knowledge sharing.

ok, thanks

After I had a fuller understanding of what you were looking for, I realized that the structure was not a good fit for Access.

You can take the code I posted and shop it around form a modification to work for your data...
Sorry I could not help more...
:-(

JeffCoachman