Add 20 to last 2 characters

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

On my Query, I would like to have a column "abcd" this
column should add 13 to the last 2
Characters from the column HDOB.
For example "12 Shevat 5763" would be "12 Shevat 5776"
"11 Adar II 5754" would be "11 Adar II 5767"
Please pay attention that the number of characters in the
column HDOB is not always the same.
How can this be done?

Some examples for HDOB column.
11 Adar II 5754
25 Adar I 5725
12 Shevat 5763
20 Heshvan 5764
18 Elul 5763

Thanks for your help.

Joe
 
On my Query, I would like to have a column "abcd" this
column should add 13 to the last 2
Characters from the column HDOB.
For example "12 Shevat 5763" would be "12 Shevat 5776"
"11 Adar II 5754" would be "11 Adar II 5767"
Please pay attention that the number of characters in the
column HDOB is not always the same.
How can this be done?

Some examples for HDOB column.
11 Adar II 5754
25 Adar I 5725
12 Shevat 5763
20 Heshvan 5764
18 Elul 5763
Click to expand...

What will you want to happen when the addition goes over the century
mark? I take it that 5690 should go to 5703?

I'd suggest updating HDOB to

Left([HDOB], Len([HDOB]) - 4) & Format(Val(Mid([HDOB], Len([HDOB]) -
4)) + 13), "0000")

Use a criterion on HDOB of

LIKE "*####"

to ensure that only properly formatted dates are edited! You may want
to store the date, month and year in three fields and concatenate them
for display. I'm familiar with Gregorian date/time math but I don't
know if internationalization has extended to Hebrew dates!
 
Your help is really appreciated.

This is how the Query looks now
SELECT TblFamily.HCName, TblFamily.DOB, TblFamily.HDOB
FROM TblClients INNER JOIN TblFamily ON
TblClients.FamilyID = TblFamily.FamilyID;

Can you please tell me how the Query should look?

I want the new column to be named BarMitzvah
This column should have the full Hebrew date, like "12
Shevat 5776"

The Active X Converter controller I'm using, don't support
any DateAdd function

Thanks

Joe
-----Original Message-----
On my Query, I would like to have a column "abcd" this
column should add 13 to the last 2
Characters from the column HDOB.
For example "12 Shevat 5763" would be "12 Shevat 5776"
"11 Adar II 5754" would be "11 Adar II 5767"
Please pay attention that the number of characters in the
column HDOB is not always the same.
How can this be done?

Some examples for HDOB column.
11 Adar II 5754
25 Adar I 5725
12 Shevat 5763
20 Heshvan 5764
18 Elul 5763
Click to expand...

What will you want to happen when the addition goes over the century
mark? I take it that 5690 should go to 5703?

I'd suggest updating HDOB to

Left([HDOB], Len([HDOB]) - 4) & Format(Val(Mid([HDOB], Len ([HDOB]) -
4)) + 13), "0000")

Use a criterion on HDOB of

LIKE "*####"

to ensure that only properly formatted dates are edited! You may want
to store the date, month and year in three fields and concatenate them
for display. I'm familiar with Gregorian date/time math but I don't
know if internationalization has extended to Hebrew dates!



.
Click to expand...
 
Thanks John

I'm using this function
Public Function AddToDate(strDate) As String

Dim strYear As String
Dim varNewYear As Variant

strYear = right(strDate, 4)

varNewYear = Val(strYear) + 13

strDate = Left(strDate, Len(strDate) - 4) & CStr
(varNewYear)
AddToDate = strDate
End Function

This works fine.

However maybe you can help me somewhere else?


My goal is to get a report for all 13th birthday by Hebrew
birthday
like 12/1/03 = 6 Kislev 5764
This report should include only birthdays for the next 2
weeks.
The Active X control I'm using, converts the date from one
box to the
other

Do you have any idea how I can get such a report?

Thanks
-----Original Message-----
On my Query, I would like to have a column "abcd" this
column should add 13 to the last 2
Characters from the column HDOB.
For example "12 Shevat 5763" would be "12 Shevat 5776"
"11 Adar II 5754" would be "11 Adar II 5767"
Please pay attention that the number of characters in the
column HDOB is not always the same.
How can this be done?

Some examples for HDOB column.
11 Adar II 5754
25 Adar I 5725
12 Shevat 5763
20 Heshvan 5764
18 Elul 5763
Click to expand...

What will you want to happen when the addition goes over the century
mark? I take it that 5690 should go to 5703?

I'd suggest updating HDOB to

Left([HDOB], Len([HDOB]) - 4) & Format(Val(Mid([HDOB], Len ([HDOB]) -
4)) + 13), "0000")

Use a criterion on HDOB of

LIKE "*####"

to ensure that only properly formatted dates are edited! You may want
to store the date, month and year in three fields and concatenate them
for display. I'm familiar with Gregorian date/time math but I don't
know if internationalization has extended to Hebrew dates!



.
Click to expand...
 
Thanks John

I'm using this function
Public Function AddToDate(strDate) As String

Dim strYear As String
Dim varNewYear As Variant

strYear = right(strDate, 4)

varNewYear = Val(strYear) + 13

strDate = Left(strDate, Len(strDate) - 4) & CStr
(varNewYear)
AddToDate = strDate
End Function

This works fine.
Click to expand...

I'd dim lngNewYear As Long rather than using a (less efficient)
Variant, but other than that this looks fine.
However maybe you can help me somewhere else?


My goal is to get a report for all 13th birthday by Hebrew
birthday
like 12/1/03 = 6 Kislev 5764
This report should include only birthdays for the next 2
weeks.
The Active X control I'm using, converts the date from one
box to the
other

Do you have any idea how I can get such a report?
Click to expand...

You have an ActiveX which can convert an arbitrary Gregorian date to
an arbitrary Hebrew date or vice versa? Cool!

I'm not aware of a version of DateAdd which works with Hebrew dates
(which does not mean that there isn't one!) but if you have a function
which does this conversion, you could use the date converted by the
function above; convert it to Gregorian as a calculated field; and use
a criterion of

BETWEEN Date() AND Date() + 14

on the Date/Time field.

And... mazel tov!
 
John thanks for the Mazel tov and your help.
However, this converter is exporting the dates, as a text
format not a date format. So I can't use the Date()
function.
Do you have any idea hoe this can be done?

Thanks
Joe
 
John thanks for the Mazel tov and your help.
However, this converter is exporting the dates, as a text
format not a date format. So I can't use the Date()
function.
Do you have any idea hoe this can be done?
Click to expand...

Use the Date() function *as a criterion to select the records* - you
needn't export it. Just uncheck the show box on the field.
 
John, for some reason your reply didn't show up on my
browser until after I made a new post with subject "Filter
question" in which I explained my question with more
clarifications.

I'm reading now your response about the date() function.
However, if you read my new post at
http://communities.microsoft.com/NewsGroups/previewFrame.as
p?
ICP=GSS3&sLCID=US&sgroupURL=microsoft.public.access.queries
&sMessageID=%253C03d801c3b919%252431861e60%
(e-mail address removed)%253E

You will understand my question. I'm not tiring to export
new.

Can you please how I can use the date function for a text
format?

Thanks

Joe
 
Back
Top