SQL to retrieve records where primary key field is date in mmmm yyyy format (2007)

[AOB]

Hi guys,

I have a table with an indexed date field (duplicates not allowed). The field is formatted to "mmmm yyyy"; i.e., month only. The idea being that there can only be one record per month.

Now I want to be able to pull data from this table using an SQL string constructed in Excel - but having trouble putting the SQL together such that it returns records as expected.

Can someone help me please?

I've tried :

SELECT [SpecificField] FROM [SpecificTable] WHERE [Month]=#September 2012#

SELECT [SpecificField] FROM [SpecificTable] WHERE [Month]=#2012-09#

SELECT [SpecificField] FROM [SpecificTable] WHERE [Month]=#2012-09-01#

...all fruitless!

Any advice?? Thanks in advance!!

AOB

 

[MStef]

The advice is: Send some records from your table.

 

[AOB]

Hi MStef,

Not sure how I can attach records to this chain?...

But, a typical record would have a Month field where the data type is Date/Time and the format is "mmmm yyyy"

So this field value for the record for last month would be "September 2012" (that's what is visible in the table in Access)

Sorry, not sure how else I can describe it for you?...

AOB

 

[AOB]

Sorry, does something like this help at all?...

(I can't work off the ReportDate field as this will be the date the record was submitted, not necessarily the month for which it was submitted - there is no correlation between the two...)

Thanks!

Al

 

[AOB]

Okay sorted - have to remember that Access will still store the field as a full date, even though I only want to show it as a month.

So used DateSerial to find the first & last of the month and then changed the SQL to search between the dates :

SELECT [SpecificField] FROM [SpecificTable] WHERE [Month] Between #2012-09-01# And #2012-09-30#

(Have used the same logic when adding updating records in the table to prevent more than one entry for any given month)