?
Solved

Update Access table with Account Period based on date in table

Posted on 2014-12-31
5
Medium Priority
?
380 Views
Last Modified: 2015-01-02
I am trying to create a query that will update the accounting period based on the date in a table.

Attached is an example.
The tblCalendar has the accounting calendar.  The Date field is the first day of the accounting period.  
The tblTestDate has test data (dates).  I want to update the Period field based on the date in the TRX Date field.
What expression can I use in the qryPeriodUpdate query to return the correct period for the date in tblTestDate?

I appreciate any help.  Thank you.
LookupExample.mdb
0
Comment
Question by:jack3_99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 668 total points
ID: 40526609
Hi,

it depends on what you think what is "correct" for you.

First some points about your queries:
Avoid using VBA domain aggregate functions where not really needed. Use subqueries instead.
"Date" should NEVER be used as a field or other object name as this is a keyword in VBA. At least set such words in square brackets like "[Date]", otherwise Access may try to use the Date function instead of using the field name.
If you want to use a date field as a constant value you need to surround that by "#" and also format the string in a way Access can accept. For example, this would work:
DMin("Period","tblCalendar","[Date]>=#" & Format([TRX Date],"yyyy-mm-dd") & "#") AS Period01But I'm not sure if the result you get is the one you want. I tried this instead, maybe this is what you want:
SELECT tblTestDate.[TRX Date], (SELECT TOP 1 Period FROM tblCalendar WHERE [Date] <= [TRX Date] ORDER BY Period DESC) AS Period1, (SELECT TOP 1 Period FROM tblCalendar WHERE [Date] >= [TRX Date] ORDER BY Period) AS Period2 FROM tblTestDate;

Cheers,

Christian
0
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 664 total points
ID: 40527060
See attached. qryPeriodLookup lists all the combinations between the dates in tblCalendar and tblTestDate. Filter shows "Yes" when [TRXDate] falls between [Date] and [ToDate]. [ToDate is achived using Lookup. qryTestDatePeriods shows only the dates in qryPeriodLookup where filter ="Yes". qryPeriodUpdate uses the information found qryPeriodLookup to update tblTestDate.

Flyster
LookupExample.mdb
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 668 total points
ID: 40527952
This is quite simple as you have the week count for each period.
See attached.

/gustav
LookupExample.mdb
0
 

Author Closing Comment

by:jack3_99
ID: 40528036
Thank you all for your help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question