Solved Can a date field pull a date from the last line of a text field?

[gojets1721]

Okay so it somewhat worked...but it's kind of weird. It's leaving out the first digit of the month

Meaning if the date was "9/27/2021", it pulled in "/27/2021" or if it was "10/12/2021", it pulled in "0/12/2021"

 

[theDBguy]

Okay so it somewhat worked...but it's kind of weird. It's leaving out the first digit of the month

Meaning if the date was "9/27/2021", it pulled in "/27/2021" or if it was "10/12/2021", it pulled in "0/12/2021"

That is interesting. I guess we'll have to adjust it a little. Try replacing the +2 with +1.

 

[gojets1721]

That is interesting. I guess we'll have to adjust it a little. Try replacing the +2 with +1.

So +1 added the colon on the end of the date (i.e. 9/27/2021 = /27/2021:)

I also tried +3 but that just took away the last digit of the year (i.e. 9/27/2021 = /27/202)

Any other ideas?

 

[theDBguy]

So +1 added the colon on the end of the date (i.e. 9/27/2021 = /27/2021:)

I also tried +3 but that just took away the last digit of the year (i.e. 9/27/2021 = /27/202)

Any other ideas?

Can you give us one more example of the data in your table? The code I gave you was tested on a previous example.

 

[theDBguy]

I just gave it another try using the following query.

SELECT Table1.Resolution, 
  Left(Mid([Resolution],InStrRev([Resolution],Chr(13) & Chr(10))+2),InStr(Mid([Resolution],InStrRev([Resolution],Chr(13) & Chr(10))+2),":")-1) AS Expr1
FROM Table1;

And here's the result:

 

[gojets1721]

Okay so I was able to figure out the exact issue. See the attached example DB.

If the resolution field only has one line, then it leaves out the first digit for the month. If the resolution has 2 or more lines, then it works perfectly.

 

[theDBguy]

Okay so I was able to figure out the exact issue. See the attached example DB.

If the resolution field only has one line, then it leaves out the first digit for the month. If the resolution has 2 or more lines, then it works perfectly.

Cool. Try using this one...

SELECT tblComplaints.Resolution, 
  IIf(InStr([Resolution],Chr(13))>0,Left(Mid([Resolution],InStrRev([Resolution],Chr(13) & Chr(10))+2),InStr(Mid([Resolution],InStrRev([Resolution],Chr(13) & Chr(10))+2),":")-1),Left([Resolution],InStr([Resolution],":")-1)) AS Expr1
FROM tblComplaints;

 

[gojets1721]

That worked! Thank you so much. It's honestly unbelievable to me that you were able to figure out a solution. You saved me days of work. I am very grateful. Thank you again

 

[theDBguy]

That worked! Thank you so much. It's honestly unbelievable to me that you were able to figure out a solution. You saved me days of work. I am very grateful. Thank you again

Hi. Glad to hear we finally figured it out. Good luck with your project.