Problem with dates/append/update query

[cbende2]

Hello all.

What I'm trying to do: I have a set of records and in the records there is a field named [StatementFrequency]. [StatementFrequency] can either be "quarterly" or "annual." I'm creating an append query to append all the "quarterly" records into another table. But what I am trying to do is create a combobox that allows you to select which quarter (3/31/year, 6/30/year, 9/30/year, and 12/31/year), and then populates the [Quarter] field on my new table that just had the records added from the append query.



If you need me to try and explain it better I shall.

Thank you

EDIT: A little better explained

I have about 370 records in my [PlanTable]. [PlanTable] has about 20 fields, and one of them is [StatementFrequency]. Right now, [StatementFrequency] is populated with either "Quarterly" or "Annual." I'm creating a form that allows the user to come in, Pick which quarter he is in from a combo box(for example... 6/30/(year)), run the query, and append all the "Quarterly" records form my [PlanTable] into my [QuarterlyReportTrackingTable]. When the user appends the "Quarterly" records, I would like for the field [QuarterEndDate] on my [QuarterlyReportTrackingTable] to be populated with the value the user selected from the combo box.

Hope that clears things up a bit.

Additional Info:
I'm not experienced in VBA.

[ItsMe]

Are you familiar with VBA and using the Query Builder?

[ranman256]

Modify as you like

Public Function getQtrEndDate(ByVal pvQtr, ByVal pvYr) As Date
Dim vDat, vQtrStart, vQtrEnd
Select Case pvQtr
  Case 1
     vQtrStart = 1
     vQtrEnd = 3
     
  Case 2
     vQtrStart = 4
     vQtrEnd = 6
  
  Case 3
     vQtrStart = 7
     vQtrEnd = 9
  
  Case 4
     vQtrStart = 10
     vQtrEnd = 12
End Select
vDat = vQtrEnd & "/1/" & pvYr
getQtrEndDate = DateAdd("d", -1, DateAdd("m", 1, vDat))
End Function

[cbende2]

I'm familiar with the Query Builder, not really VBA though.

[ItsMe]

When all you have are dates, dates that span over several years, it is difficult to establish what period a given date falls in. In its simplest form, counting quarters of a calendar year is not too difficult. You may be able to do the whole thing in a query using an Instant If function IIF(). I may lean towards VBA, something like post #3. If the periods are complex because of fiscal year, FIFO, 4-4-5, period by month, etc. I will employ additional (static) tables to help queries count periods.

You may be able to accomplish an Append query without the combo. Have a date function analyze the dates and assign the period number for you in a field in the table. Now you can run reports and or view data using forms, using the period number for Grouping and or criteria.

[cbende2]

Thank you guys, this problem seems a little over my head. I'll resort to something else.

[ItsMe]

You could provide a basic explanation of what you are trying to accomplish. For instance... "I need to create a report for accounting. Sometimes they need certain periods included in the report. I then need a summary that counts occurrences of a certain task. This count also needs to be summarized by period. I was considering creating a temp table to aid in creating these reports."

I may be wrong, but this is kinda what I guess is happening.

[ranman256]

Sorry..in your query you would provide the qtr and year and the field would give the end date of the qtr.

getQtrEndDate (1,2014) as exp1

[rpeare]

You shouldn't need to paste the records then update the table, you should be able to append records and the quarter at the same time with a formula, or at the very least select the records you want to add ahead of time based on the quarter selection.

For instance, if you have a table that has an 'activity date' or something similar and you are trying to determine which quarter any given record falls in you could have an immediate if (as itsme suggested) something like

ActivityQuarter: iif(datepart("m", [ActivityDate]) between 1 and 3, "Q1",iif(datepart("m", [ActivityDate]) between 4 and 6, "Q2",iif(datepart("m", [ActivityDate]) between 7 and 9, "Q3",iif(datepart("m", [ActivityDate]) between 10 and 12, "Q41","XX")))) & "-" & datepart("yyyy", [ActivityDate])

This assumes your quarters follow a calendar year and not a fiscal year where the months are offset but you'd get something like

Q1-2014 for any record with an activity date between 1/1/2014 and 3/31/2014

[ItsMe]

...you could have an immediate if...

Did I goof up the name of the function??? Woops

[rpeare]

honestly I don't know the 'correct' name, you gave the IIF() which is exactly how I'd have approached it. Instant and Immediate are pretty much the same thing for the purposes of this example

[ItsMe]

...When the user appends the "Quarterly" records, I would like for the field [QuarterEndDate] on my [QuarterlyReportTrackingTable] to be populated with the value the user selected from the combo box....

In its simplest form you would reference the combo in the append query. You can add a new column to your query by placing syntax like this in the design grid of the query builder. (Don't know the name of your form or Combo)
AliasQtr: [Forms]![FormName].[ComboName]

That would go in the field, "Field", to create an alias named "AliasQtr"


Then, in the "Append To" field, select the name of the field you want to append to "QuarterEndDate"