How to find date data from query?

  • Thread starter Thread starter Niklas
  • Start date Start date
N

Niklas

Hi All,

Case:
table with rows...
Year (data type number),
Month1
Month2
Month3
.... (data type is currency).

How to crate a query that results only the currency of
current month?

The idea is to add a textbox on the form that shows the
charge of current month e.g. Month10 = 23 €).
 
Hi,

Part of your data is not accessible as data. A field name is NOT data,
or more precisely, SHOULD NOT BE. A normalize data should be like:


Date_and_time Amount 'fields name
2000.01.01 10:10:10 230.45 $
2000.02.01 11:11:11 87.67 $
....


and then, your query would be simply:

SELECT amount
FROM myTable
WHERE Month( Now) = Month( Date_and_time)



Actually, with your design, you can try to produce the TEXT of the SQL
statement at run time

strSQL= "SELECT Month" & Month( Now) & " FROM myTable WHERE [Year]=" &
Year( Now )
Set rst=CurrentDb.OpenRecordset( strSQL )


or you can try to normalize your data:


SELECT DateSerial( [Year] , 1, 0) As DateAndTime, Month1 As Amount FROM
myTable
UNION ALL
SELECT DateSerial( [Year] , 2, 0) , Month2 FROM myTable
UNION ALL
SELECT DateSerial( [Year] , 3, 0) , Month3 FROM myTable
....
UNION ALL
SELECT DateSerial( [Year] , 12, 0) , Month12 FROM myTable


and next, use that saved query instead of the table in the first SQL
statement I presented.




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top