Getting Current Month and Year in a select query string

[AT76]

Hi,

I have the following SQL string in access:

select * from TableName(M200601) where ....

The portion I'm interested in editing is the date: "M200601". This is hard coded right now into the query. I would like for the year and month to be recognized automatically preceded by an M and passes into the Select string. Is there a way to do this?

Thank you for any input provided!

- AT

 

[AT76]

I would like to clarify that I'm doing this in Access using the SQL View option.

 

[NvrSaDi]

You can create a new field with a concatenation of data. Assuming that you have a field named [myDate], your new field would look something like this: NewField: "M" & [myDate]. Make sure that you have spaces between the & and the concatenated data.

As an alternative, you can paste the query SQL statement into a procedure and then concatenate a variable into the SQL string, if you are using action queries. Haven't tried it with select query. This becomes a two-step process of creating a new table with an action query and then using a select query to pull data from the new query.

 

[AT76]

Ok, this is starting to make sense.

I created a new field in my form and made it =Date(). Is the a way to format this field to give me YYYYMM (200602).

Also I created another field and concatenated the M and the new M as you instructed. Here's what I have so far: M2/15/2006. All I need to do is change this to M200602.

If I'm correct will I then pass the name of this new field to my SQL string?

Thank you so much!

 

[PHV]

Have a look at the Format function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AT76]

Got it PHV: =Format(Date(),"yyyymm")

Thank you.

 

[NvrSaDi]

When you open the SQL statement behind the query you will see a new field (using above example) has been added that looks like this -- "M" & [myDate] AS NewField

 

[AT76]

not sure I quite understand AccessDB.

Would this work?


select * from TableName([Forms]![F_MainForm]![tableDate]) where

The field is on my form: F_MainForm.

field name: tableDate

I tried it but it didn't work. Am I going at it the wrong way?

 

[AT76]

Correction: The control source of the new created field in my form is: ="M" & [myDate]

 

[NvrSaDi]

If you are concatenating the "M" with a date in a textbox in a form, the calculated field would look something like this -- NewField:"M" & [Forms]![F_MainForm]![tableDate].
HOWEVER: I suspect that the results of your query will be the same for all of the records in the calculaated NewField field. Both of the concatenated values are fixed.

 

[PHV]

select * from TableName(M200601) where
Syntax error
select * from TableName([Forms]![F_MainForm]![tableDate]) where
Obviously, same syntax error
Furthermore a parameter in a query is for VALUES, not for keywords nor names.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AT76]

PHV,

This is the actual query:

select R3ITID, count(r3orln) as NARSLines from hstdtaasi.dopp0062(M200601) where cmalpt not in ('X','Y','Z') AND EXRCTP = 'C' and exstki = 'S' group by R3ITID

I'm trying to immitate this query but replacing (M200601) with code to bring in the current Year and Month.

The above query does work for M200601 and M200602.

Here's what I tried doing:

select R3ITID, count(r3orln) as NARSLines from hstdtaasi.dopp0062([Forms]![F_MainForm]![tableDate]) where cmalpt not in ('X','Y','Z') AND EXRCTP = 'C' and exstki = 'S' group by R3ITID

 

[PHV]

The above query does work for M200601 and M200602
A passthrough query ? To which DBMS ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AT76]

This query is grabbing data from an AS400 server

 

[AT76]

Am I forgetting some syntax with: ([Forms]![F_MainForm]![tableDate])

I tried this syntax in vba and the value passed is M200602 as expected.

Do I need to add anything extra for SQL View's format in access?

 

[PHV]

a parameter in a query is for VALUES, not for keywords nor names

Furthermore, passthrough queries IGNORE any parameter (hey, passthrough ...)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AT76]

Got it PHV... I guess my approach will not work by what you mentioned. Any other thoughts as to how I might do this?

 

[PHV]

Play with the SQL property of the DAO.QueryDef object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886