Period to Period (1 Viewer)

[vvasudev]

Hello All,

I have a table called Central_Currency which has got the following fields

Product: Text
Price : Number
Currency : Text
Dollar_Equal: Number
Region : Text
Month : Text
Year : Number

I have designed a form with combo boxes for the Product, Region, Month and Year. I have comboxes called Year1,Year2,Month1,Month2 for the Year and the Month respectively. I use the followin code to pull out records from between a specefic month and Year and Between another month and Year, Like all records from January 2009 to December 2010

SELECT C.Product, C.Price, C.Currency, C.Dollar_Equal, C.Region, C.Month, C.Year, GCC.Name
FROM GCC INNER JOIN Central_Currency AS C ON GCC.Products = C.Product
WHERE ((([C.Year])=[forms]![YearToYear]![Year1]) AND (([C.Month])>=[forms]![YearToYear]![Month1])) OR ((([C.Year])=[forms]![YearToYear]![Year2]) AND (([C.Month])<=[forms]![YearToYear]![Month2])) OR ((([C.Year])>[forms]![YearToYear]![Year1] And ([C.Year])<[forms]![YearToYear]![Year2]))
GROUP BY C.Product, C.Price, C.Currency, C.Dollar_Equal, C.Region, C.Month, C.Year, GCC.Name, C.Month, C.Year
HAVING (((C.Region) Like "*" & [Forms]![YearToYear]![Region] & "*") AND ((GCC.Name) Like "*" & [Forms]![YearToYear]![Product] & "*"));

I got some intial help from the forumn , however this code doesnt seem to work. I am very new to Programming enviroment and any help would be really appreciated.


Regards,
Vineeth Vasudevan

 

[namliam]

You need to convert your year/month to a proper date so you can use its functionality

Use dateserial function to convert it to a date then the rest should be easy.

 

[Brianwarnock]

The original thread had a number of suggestions, however all assumed a month number, I however did put the thought that the month might be text. We recieved no repy. If you do not respond to people they cannot help you.

As mentioned on the other thread it would be far better to have a date field, date fields can easily be parsed into the separate parts , but allow all kinds of simple operations. As you have a text month you are going to have to build a date field in the query to compare with.

You should not use field names of Month and Year as they are ACCESS reserved words and it will cause you problems , however as you have I will

Workingdate: CDate(1 & "/" & [month] & "/" & [year])

will give you a date for the 1st of each month, uk format, and you can then use Between... And.. to pull the range you require.

Brian

 

[Brianwarnock]

Namliam I don't believe that Dateserial allows the use of text months.

Brian

 

[vvasudev]

Hi Brian,

Thank you for your quick reponse,Cdate function seems to work but, can u tell me how to incorporate it into the query that i have written. sory for the delayed reply, thank you also to namliam

 

[Brianwarnock]

You put what I wrote into a new column in the design grid.

Brian

 

[vvasudev]

Many thanks Brian,

i have managed to figure it out as per ur directions..Cheers

 

[vvasudev]

Hi,

i have put in the following code, but the between operator doesnt seem to do the trick

SELECT Central_Stock_Table.[Product Description], GCC.Name, Products.Speciality, Central_Stock_Table.[Opening Stock], Central_Stock_Table.Receipts, Central_Stock_Table.[Private Sales], Central_Stock_Table.[Ex-Local Sales], Central_Stock_Table.[FOC Out], Central_Stock_Table.[Samples Out], Central_Stock_Table.Adjustments, Central_Stock_Table.[Closing Stock], Central_Stock_Table.Distributor, Central_Stock_Table.Region, Central_Stock_Table.Month, Central_Stock_Table.Year, CDate(1 & "." & [month] & "." & [year]) AS [Date Of import]
FROM Products INNER JOIN (GCC INNER JOIN Central_Stock_Table ON GCC.Products = Central_Stock_Table.[Product Description]) ON Products.Products = GCC.Name
GROUP BY Central_Stock_Table.[Product Description], GCC.Name, Products.Speciality, Central_Stock_Table.[Opening Stock], Central_Stock_Table.Receipts, Central_Stock_Table.[Private Sales], Central_Stock_Table.[Ex-Local Sales], Central_Stock_Table.[FOC Out], Central_Stock_Table.[Samples Out], Central_Stock_Table.Adjustments, Central_Stock_Table.[Closing Stock], Central_Stock_Table.Distributor, Central_Stock_Table.Region, Central_Stock_Table.Month, Central_Stock_Table.Year, CDate(1 & "." & [month] & "." & [year])
HAVING (((CDate(1 & "." & [month] & "." & [year])) Between [Forms]![Stcok_Reports]![Date1] And [Forms]![Stcok_Reports]![Date2]));

any thoughts?
regards,
Vineeth

 

[vvasudev]

I have modified the query like this

SELECT Central_Stock_Table.[Product Description], GCC.Name, Products.Speciality, Central_Stock_Table.[Opening Stock], Central_Stock_Table.Receipts, Central_Stock_Table.[Private Sales], Central_Stock_Table.[Ex-Local Sales], Central_Stock_Table.[FOC Out], Central_Stock_Table.[Samples Out], Central_Stock_Table.Adjustments, Central_Stock_Table.[Closing Stock], Central_Stock_Table.Distributor, Central_Stock_Table.Region, Central_Stock_Table.Month, Central_Stock_Table.Year, Format(CDate(1 & "." & [month] & "." & [year]),"dd.mm.yyyy") AS [Date Of import]
FROM Products INNER JOIN (GCC INNER JOIN Central_Stock_Table ON GCC.Products = Central_Stock_Table.[Product Description]) ON Products.Products = GCC.Name
GROUP BY Central_Stock_Table.[Product Description], GCC.Name, Products.Speciality, Central_Stock_Table.[Opening Stock], Central_Stock_Table.Receipts, Central_Stock_Table.[Private Sales], Central_Stock_Table.[Ex-Local Sales], Central_Stock_Table.[FOC Out], Central_Stock_Table.[Samples Out], Central_Stock_Table.Adjustments, Central_Stock_Table.[Closing Stock], Central_Stock_Table.Distributor, Central_Stock_Table.Region, Central_Stock_Table.Month, Central_Stock_Table.Year, Format(CDate(1 & "." & [month] & "." & [year]),"dd.mm.yyyy")
HAVING (((Format(CDate(1 & "." & [month] & "." & [year]),"dd.mm.yyyy")) Between [a] And ));

but it doesnt seem to work if i put 01.01.2009 and 01.01.2010

 

[vvasudev]

Hi all,

has someone got any insights on this one?..any help would be appreciated


regards,
Vineeth

 

[namliam]

Workingdate: CDate(1 & "/" & [month] & "/" & [year])

Your right Dateserial dont work with written months, didnt quite notice that

I have modified the query like this
but it doesnt seem to work if i put 01.01.2009 and 01.01.2010

Using the format totaly defeats the purpose, a date works as a date, but as a string there is nearly nothing between 01.01.2009 and 01.01.2010, as strings will compare per character thus only allow anything between the 09 and 10

You will need to do the "reverse"
HAVING CDate(1 & "." & [month] & "." & [year]) Between cdate([a]) And cdate()

Please take note of Access' special use of the US date in queries though if you use 01 and 31 only and/or full months anyways it should never be a problem.

PLEASE also DO NOT splash unformatted SQL Onto the forum
At minimum do a little formatting like:

SELECT 
      Central_Stock_Table.[Product Description], 
      GCC.Name, 
      Products.Speciality
...
FROM      Products 
INNER JOIN (GCC 
..
GROUP BY 
     Central_Stock_Table.[Product Description], 
     GCC.Name,
....
HAVING Format(CDate(1 & "." & [month] & "." & [year]),"dd.mm.yyyy")Between [a] And [b]);