now and insert date/quarter parameter

[John1Chr]

I want to insert a parameter in a table using an update query that is based off of the time when you run it. The results will be the current fiscal year and quarter. Lets say you run the query today (9/29/06) then the query would give 200603 (current Fiscal year and quarter):

Before 3/31/2006 gives 200601 (gives first qtr of 2006)
Before 6/30/2006 gives 200602 (gives second qtr of 2006)
Before 9/30/2006 gives 200603 (gives third qtr of 2006)
Before 12/31/2006 gives 200604 (gives fourth qtr of 2006)

Is there a simple function or sql that can give you these parameters?

 

[Golom]

Format(Date(),"yyyy") & "0" &  Format(Date(), "q")

 

[John1Chr]

Golom,

R u thinking of running it twice? Insert the now date in the field first and then run this function that u gave me against it. Is there a function to do it in one shot?

 

[Golom]

I just gave you an expression that produces the string that you want. In an UPDATE query

UPDATE myTable

SET YrQtr = Format(Date(),"yyyy") & "0" &  Format(Date(), "q")

Would update the "YrQtr" field in every record in the table to "200603" if it was run today.

I don't know what you mean by "... Insert the now date in the field first ...". This update will set the field values to the value returned by the expression regardless of what might have been in the field before it was run.

 

[John1Chr]

Oh,

Nothing was in the field prior to running the query. I want to update the null records with the fy and qtr going forward when the query is run. Once the fields are updated with the qtr and fy I will not run against those records again. (I want the query to ignore the records that were updated.) I want the function to update the null fields with the time that the query is run. Clear as mud?

 

[Golom]

OK

UPDATE myTable

SET YrQtr = Format(Date(),"yyyy") & "0" &  Format(Date(), "q")

Where YrQtr IS NULL

 

[John1Chr]

Golom,

I am still learning VB. My table name is tblA and the field I want to update is RDt on that table. How would the entire code that you write look in a module. I am still using some old code that you wrote awhile back to create new functions but I am rusty in this area. Thanks again!

 

[Golom]

In a module? ... you mean as VB code that you run rather than as SQL code in the query SQL view as it appears above?

Sub cmdUpdate_Click()
Dim SQL As String

SQL = "UPDATE tblA " & _
      "SET RDt = '" & _
       Format(Date(),"yyyy") & "0" &  Format(Date(), "q") & "' " & _
      "WHERE RDt IS NULL"

[COLOR=black cyan]' Uncomment the next line if you're running DAO[/color]
[COLOR=black cyan]' CurrentDb.Execute SQL[/color]

[COLOR=black cyan]' Uncomment the next line if you're running ADO[/color]
[COLOR=black cyan]' CurrentProject.Connection.Execute SQL[/color]

End Sub

 

[John1Chr]

Golom,

If u come to Minnesota I will buy u a beer. Thanks, again.

 

[Golom]

If I have to come all that way I expect at least a 6-pack.