Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date format query

Status
Not open for further replies.

acr1

Programmer
Oct 12, 2001
73
NZ
Team
I have a date field that I wish to convert to a Month,currently as dd/mm/yy format. I then wish to assign a number/variable to that month....

The month formula field does not like me.....or want to work and Seagate Info Vs 7.0 help is not very specific.

an example is If the Month is April then it is 20; If May then 21.

The numbering sequence is inherited so I cannot change to like April is 04 and May is 05...etc.

Once again TIA.
 
What's the deal with those month numbers? Does each month always have the same number associated with it; so Jan is always 17, Feb 18, etc...

Or do the months roll, so if Dec is 28 and the report rolls over onto the next year, Jan is then 29?

If the months are static, try:

month({Orders.Order Date})+16

If the months roll, then;

//@Form1
whileprintingrecords;
numbervar yearendchecker := 0;

if month({Orders.Order Date}) = 12
then yearendchecker = 1;

//@Form2
whileprintingrecords;
numbervar yearchecker;

if yearendchecker = 1
then
month({Orders.Order Date})+28
else
month({Orders.Order Date})+16

Naith
 
Naith
Thankyou.
The number is variable between contracts so is unlikely to be the same all the time. However all I want to do is if the Month is April then assign the appropriate number.. the rest of the months numbering should then follow in sequence.....I need to populate a field in a report with the appropriate number based on a date field as in previous correspondence.

I am a tad confused with your reply. Can it be simplified...
TIA


 
acr1,

what does your formula currently look like? Are you, by chance, adding your numeric field to your month. Remember the month function returns a number and you may do doing math (adding the value of month to the numeric field) instead of concatenating the values.

ie. month = April and numeric = 17
04 + 17 = 21

did you want 0417?

I may be way off because I'm not clear on the problem.

In any case, good luck.

bill
 
How do you propose assigning this number to the month if it changes?

If you want to allow for changing the month starting number dynamically, just create a parameter field to enter the starting month number and increment from there.

This statement is worrisome:

"The month formula field does not like me.....or want to work and Seagate Info Vs 7.0 help is not very specific."

State what errors you get, and the formula used. The folk here are quick at resolving errors if you give them a fighting chance.

Perhaps your date is a string, so you'd need to convert it to a date prior to using the month function (it does work).

I could take more potshots at this, but it would be more timely if you gave som example data, explain how the starting month is derived, and the expected output.

-k kai@informeddatadecisions.com
 
Apologies to respondees for my lack of clarification.

I want to return the Month value by specifying a date. I insert the qry field within the Month() as below and it is still asking me to insert a date....

Month ({Query.ACTUAL_COMP_DATE})

I am unsure as to what the 'Month' syntax returns as a value..I assume is is between 1-12.

I need to setup a Program Month in a report based on the Month value produced.

Behind it all for example ....a 3yr Contract starts in July 2002. July 2002 is Program Month 1...June 2005 is Program Month 36.

Hope that this is clearer..
TIA
 
So...are you saying that the month of the contract start is always equal to 1 regardless of the calendar month, and the contract end date month is the count of months thereafter?

In that case, you might as well drop the month() theory. (Incidentally, the reason the month function isn't doing the do for you is because you're using a Query. I didn't know that before. For future reference, you're right; the month function returns a value of 1-12, according to the month in question.)

Convert your date strings to dates, and run a date difference, incrementing the value by 1 to get your contract end month.

Like this:
(Using a date format of dd/mm/yy)

//@F1
cdate(mid({Query.Order Date},1,8))

//@F2
cdate(mid({Query.Required Date},1,8))

//@F3
whileprintingrecords;
numbervar contract_start_month := 1;
numbervar contract_end_month :=
datediff('m',{@F1},{@F2})+1

Haven't given you the months inbetween, because I'm assuming you just want the total range rather than all months in the range incremented by one.

Naith
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top