Querycolumn SQL-problem

[Willem!]

Hi!

I have a table with 2 columns, 'year' and 'month'. I want to create a query which creates a date from these two numbers.

In the design view of the query, my current code in the field-cell is:

Datum: DateSerial([SELECT Year FROM QueryTotal];[SELECT Month FROM QueryTotal];1)

QueryTotal is a query which contains all the data.

When I run the query access asks me to enter a parameter value for year and month... So it is not working, what am I doing wrong? Anyone a suggestion?

I am rather new in SQL-stuff, perhaps I am overlooking the obvious... please inform me!

Willem!

 

[Smart]

Datum: DateSerial([SELECT Year FROM QueryTotal];[SELECT Month FROM QueryTotal];1)

create a new column in your query yourYear: format(yourdatefield,"YYYY")
create a new column in your query yourMonth: format(yourdatefield,"MM")

reference these new fields in your code

 

[Willem!]

Hi Smart,

good suggestion! However, it is still not working. When i create another column that incorporates the following code:

YearDate: Format([SELECT Year FROM QueryTotal];"yyyy")

...and when I run the query Access wants me to enter a paramater-value for the year.

It seems that for some reason the Query cannot find the correct year.

Or did I understand your last reply not enough?

 

[neileg]

[SELECT Year FROM QueryTotal]
It's this bit that is wrong. It is the name of the field that contains the year that goes here not a select statement.

 

[Willem!]

Thanks for your reply,
my current code is:

Datum: DateSerial([QueryTotal]![Jaar];[QueryTotal]![Maand];1)

('Jaar' and 'Maand' are Dutch words for year and month)

However, when I let the query run, I get the same error, Access wants me to enter a paramater-value for the year (and probably also month).

Is it okay that the column 'Datum' is in the same query (QueryTotal) as month and year?

The answer lies before me, only I don't see it yet.

 

[neileg]

A few things here need looking at.

Some words should not be used as object names. They are usually referred to as reserved words and relate to functions or objects in VBA. In English, year and month are reserved words, so it may be the same in Dutch.

A query can be based on tables and/or other queries but it can't be based on itself. If Jaar and Maand exist as fields in the tables on which you query is based, that's fine. If they are not, you may need to create a nested query. This means creating one query and then basing a second on that.

Finally, Access assumes that any object name that it doesn't recognise is a parameter. It can't find Jaar or Maand in the dataset so it asks for a parameter.

 

[Willem!]

Thanks Neil! My code works now!

1) I'm aware of the reserved words, however, I am using an English version of Access, so I can use the Dutch words without any problems (which is quite nice actually)

2) thanks for this! I assumed the Query could be partial based on itself, a bit like how you can use VBA (but I'm not sure this is a very valid comparison). Now I refer to another query and everything works just fine. (I must say I was surprised that it worked because I sincerely believed that I tried that option already, how one can be mistaken!)

3) thanks for this bit of fundamental knowledge

So summarized: many, many thanks for your help, I was puzzling about it all morning!

Cheers,
Willem