split syntax

[Khunter]

Hi, new to access. however I'm pretty good with excel. I I'm not sure the correct syntax for certain functions.

I want to take this string #2 (10/30-11/05) and break it up into separate dates.

I don't have a "split" function since I think its not with 2007.

Any help would be appreciated.

[JoeM]

You should be able to use the LEFT, RIGHT, MID functions along with INSTR to find the "-" and the "(" and ")" to do what you want (see: https://www.techonthenet.com/access/...ring/instr.php).

If all the strings are exactly the same length, you probably won't even need to use that. You can just use the MID function.

If you still need help, please list multiple examples of entries. Try to represent all the different scenarios of what the data may look like.

[Khunter]

samplecodeDATE.zip

Thanks for the response. here is what I have. in excel. And I've tried to work it out in access

[Khunter]

So yes that's what I have been trying to do. however I dropped my excel code and that's what I'm trying to replicate in access

You should be able to use the LEFT, RIGHT, MID functions along with INSTR to find the "-" and the "(" and ")" to do what you want (see: https://www.techonthenet.com/access/...ring/instr.php).

If all the strings are exactly the same length, you probably won't even need to use that. You can just use the MID function.

If you still need help, please list multiple examples of entries. Try to represent all the different scenarios of what the data may look like.

[June7]

In Access textbox on form or report:

=CDate(MID([fieldnamehere],5,5) & "/2018")
=CDate(MID([fieldnamehere],11,5) & "/2018")

In a query, remove the = sign.

However, if the sequence number can exceed 9, this will get more complicated. Will need InStr() function. Let x represent the field:

=CDate(MID(x, InStr(x, "(")+1,5) & "/2018")
=CDate(MID(x, InStr(x, "-")+1,5) & "/2018")

Split() function would be in VBA, not Excel formula, and would not be useful for this situation.

[JoeM]

I do not have the ability to download files from present location (corporate security policy), but looks like June was able to come up with something for you.
Please let us know if that works for you.

[Khunter]

Ok thank this works.
I'm am also trying to convert a day of the week into an integer.
Tuesday =1, Wednesday =2 ect.
I have a column in a query that produces a day of the week from the DB.
Monday, Tuesday ect.

I want to add the value of the specific day with the week starting on Tuesday to the beginning date. Produced by this code "CDate(MID(x, InStr(x, "(")+1,5) & "/2018")"


So if the code produced 10/23/2018 and the Day of the week in the other column is Wednesday the new date should be 10/24/2018.
Hope this makes sense.

Thnaks for help again!


I'm still not sure how to look for and or change data types yet.

In Access textbox on form or report:

=CDate(MID([fieldnamehere],5,5) & "/2018")
=CDate(MID([fieldnamehere],11,5) & "/2018")

In a query, remove the = sign.

However, if the sequence number can exceed 9, this will get more complicated. Will need InStr() function. Let x represent the field:

=CDate(MID(x, InStr(x, "(")+1,5) & "/2018")
=CDate(MID(x, InStr(x, "-")+1,5) & "/2018")

Split() function would be in VBA, not Excel formula, and would not be useful for this situation.

[June7]

If Wednesday = 2, then adding that to 10/23/2018 will be 10/25/2018, not 10/24/2018.

I think the closest to Match() in Access is Switch().

Switch(DOW="Tuesday",1, DOW="Wednesday",2, DOW="Thursday",3, DOW="Friday",4, DOW="Saturday",5, DOW="Sunday",6, DOW="Monday",7)

Alternatively, build a table with this data and join tables in query to retrieve the day number.

Calculating ActualDate:

WkStrt - Weekday(WkStrt, 3) + IIf(Weekday(WkStrt, 3) > DayNum, 7 + DayNum, DayNum)

[Khunter]

After I sent the response, I actually just made a separate table with Weekdays Tues-Mon and a col with values 1-7. then linked them.
That worked out well. Thank you for your help.

Ok thank this works.
I'm am also trying to convert a day of the week into an integer.
Tuesday =1, Wednesday =2 ect.
I have a column in a query that produces a day of the week from the DB.
Monday, Tuesday ect.

I want to add the value of the specific day with the week starting on Tuesday to the beginning date. Produced by this code "CDate(MID(x, InStr(x, "(")+1,5) & "/2018")"


So if the code produced 10/23/2018 and the Day of the week in the other column is Wednesday the new date should be 10/24/2018.
Hope this makes sense.

Thnaks for help again!


I'm still not sure how to look for and or change data types yet.