Solved

Update Access table with Account Period based on date in table

Posted on 2014-12-31
5
343 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 167 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 166 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 50

Accepted Solution

by:
Gustav Brock earned 167 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

Technology Partners: 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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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.

733 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