Help (1 Viewer)

[vvasudev]

Hello,

I have got a table called Central_Currency which has around 10 fields. Now among the 10 fields 2 are the Month and the Year. I want to know how i would be able to display all the 10 fields from a particualr year and month to another particular year and month. The users get to choose the Months and Years from comboboxes..i am a beginner and any help would be appreciated

regards,
Vineeth

 

[namliam]

Have a search on the forum for "Search form" you should find some excelent samples.

 

[Brianwarnock]

Gosh this is messy without a single date field which allows for the use of Between... and...

The where clause in a query would be
(Fldyear >= forms!formname!yrfromcomboname and <= forms!formname!yrtocomboname) And (fldmonth >= forms!formname!mthfromcomboname and <= forms!formname!mthtocomboname)

Brian

 

[vvasudev]

Many thanks for the quick reponses..really appreciate the help

 

[namliam]

Gosh this is messy without a single date field which allows for the use of Between... and...

The where clause in a query would be
(Fldyear >= forms!formname!yrfromcomboname and <= forms!formname!yrtocomboname) And (fldmonth >= forms!formname!mthfromcomboname and <= forms!formname!mthtocomboname)

Brian

for an exact month that would work more or less, but this would suffice too
Fldyear = forms!formname!yrfromcomboname and
fldmonth = forms!formname!mthfromcomboname


If you want a period this would fail misserably though

 

[Brianwarnock]

I don't think my solution does work properly, nothing to test against of course, but I think it should be

(Fldyear >= forms!formname!yrfromcomboname and fldmonth >= forms!formname!mthfromcomboname) And (Fldyear <= forms!formname!yrtocomboname and fldmonth <= forms!formname!mthtocomboname)

Brian

 

[stopher]

I took a slightly different approach but it still doesn't look pretty (it assumes year and month are numbers)

[fldYear]*100+[fldmonth]
Between
[forms]![formname]![yrfromcomboname]*100+[forms]![formname]![mthfromcomboname]
And
[forms]![formname]![yrtocomboname]*100+[forms]![formname]![mthtocomboname]

Chris

 

[Brianwarnock]

Chris that would rely on months before October having a leading 0 , I think. I wonder if the Months are Text?

If I were going to adopt a creative approach i would use Dateserial to build dates.

Brian

 

[stopher]

Chris that would rely on months before October having a leading 0 , I think. I wonder if the Months are Text?

If I were going to adopt a creative approach i would use Dateserial to build dates.

Brian

If numbers then its fine. The *100 deals with the absence of a leading zero.

Jan 2009 would be 2009*100 + 1 = 200901

Oct 2009 would be 2009*100 + 10 = 200910

If text then we can concatenate (assuming there's a leading zero)

Chris

 

[namliam]

I don't think my solution does work properly, nothing to test against of course, but I think it should be

Which is what I meant with the "more or less"

If I were going to adopt a creative approach i would use Dateserial to build dates.

That would be my prevered approach, better yet, dont store year/month to start with

 

[Brianwarnock]

Gotcha, actually that's neat.
By text I meant January February etc

Brian

 

[Brianwarnock]

Gosh this is messy without a single date field which allows for the use of Between... and...


Brian

Namliam I agree with that sentiment.

Brian

 

[stopher]

I don't think my solution does work properly, nothing to test against of course, but I think it should be

(Fldyear >= forms!formname!yrfromcomboname and fldmonth >= forms!formname!mthfromcomboname) And (Fldyear <= forms!formname!yrtocomboname and fldmonth <= forms!formname!mthtocomboname)

Brian

I reckon doing it the logic route would have to be something like:

[Fldyear]=[forms]![formname]![yrfromcomboname] AND [fldmonth]>=[forms]![formname]![mthfromcomboname]
OR
[Fldyear]=[forms]![formname]![yrtocomboname] AND [fldmonth]<=[forms]![formname]![mthtocomboname]
OR
[Fldyear]>[forms]![formname]![yrfromcomboname] And [Fldyear]<[forms]![formname]![yrtocomboname];

i.e. deal with the three cases:
- the start year (months >=)
- the end year (months <=)
- the years in between (all months)

I'm sure there must be a better way! I wonder if using NOT helps?

Chris

 

[stopher]

Gotcha, actually that's neat.
By text I meant January February etc

Brian

Lol, long text = big headache.

 

[vvasudev]

hello all,
I am using this but it doesnot seem to HELP..any thoughts??

SELECT Central_Currency.Month, Central_Currency.Year
FROM Central_Currency
GROUP BY Central_Currency.Month, Central_Currency.Year
HAVING (((Central_Currency.Month) Between [Forms]![Yeartoyear]![Month1] And [Forms]![Yeartoyear]![Month2]) AND ((Central_Currency.Year) Between [Forms]![Yeartoyear]![Year1] And [Forms]![Yeartoyear]![Year2]));

 

[namliam]

HAVING (((Central_Currency.Month) Between [Forms]![Yeartoyear]![Month1] And [Forms]![Yeartoyear]![Month2]) AND ((Central_Currency.Year) Between [Forms]![Yeartoyear]![Year1] And [Forms]![Yeartoyear]![Year2]));

That wont work if you have something like from: 2009 12 to 2010 01, because there is no month between 12 and 1.

You need to convert it to a real date or to a number as per described in this thread

 

[Brianwarnock]

Amazing asking for help and then totally ignoring all suggestions.

Brian