Replace function not working as expected

[MrBedo]

I have a table called 'Logon' which contains 6 columns. The first column contains a date in format DD/MM/YYYY.

Some of the dates are preceeded by the day name, for example 'Mon', 'Tue' etc. I need to remove the preceeding day names and so have created a make take query. The query looks at the 'Logon' table makes a table called 'LogonQ'.

I use the Replace function in the first column (named Field1) but whenever I run the query it creates the 'LogonQ' table minus any rows which contain a day name in column 1.

The code I'm using in the 'Criteria' section of 'Field1' in the make table query is:

Replace([Field1],"Mon ","") And Replace([Field1],"Tue ","") And Replace([Field1],"Wed ","") And Replace([Field1],"Thu ","") And Replace([Field1],"Fri ","") And Replace([Field1],"Sat ","") And Replace([Field1],"Sun ","")

Obviously I'm being a nonce, but I'm at a loss to see where!

If anyone can help or if I can clarify anything then it would really be appreciated.

thanks

 

[Trevor G]

Why not use the right function and count the characters you need and that should strip away the days.

SELECT tblLogon.LodOnDate, Right([LodOnDate],11) AS NewDOB
FROM tblLogon;

 

[MrBedo]

You know what? I'm actually using a similar statement in another column to strip trailing milliseconds, so quite why it didn't occur to me to try that on the date column......it's been a long week already, that's my excuse!

Thanks Trevor, your help's much appreciated.

 

[MSAccessRookie]

I have a table called 'Logon' which contains 6 columns. The first column contains a date in format DD/MM/YYYY.

Some of the dates are preceeded by the day name, for example 'Mon', 'Tue' etc. I need to remove the preceeding day names and so have created a make take query. The query looks at the 'Logon' table makes a table called 'LogonQ'.

I use the Replace function in the first column (named Field1) but whenever I run the query it creates the 'LogonQ' table minus any rows which contain a day name in column 1.

The code I'm using in the 'Criteria' section of 'Field1' in the make table query is:

Replace([Field1],"Mon ","") And Replace([Field1],"Tue ","") And Replace([Field1],"Wed ","") And Replace([Field1],"Thu ","") And Replace([Field1],"Fri ","") And Replace([Field1],"Sat ","") And Replace([Field1],"Sun ","")

Obviously I'm being a nonce, but I'm at a loss to see where!

If anyone can help or if I can clarify anything then it would really be appreciated.

thanks

I have highlighted the And statements above, and based on the description of your issue, it is doubtful that all of the defined conditions will be true at the same time. Since that is what would be required for your statement to work, it would never be true. Changing And to Or could resolve the problem and allow the statement to work as intended.

-- Rookie

NOTE:

I see that you have determined a way to resolve your issue. I was only trying to point out what I think may have been wrong with your original Query.

Drop by and visit again any time.

 

[Trevor G]

You know what? I'm actually using a similar statement in another column to strip trailing milliseconds, so quite why it didn't occur to me to try that on the date column......it's been a long week already, that's my excuse!

Thanks Trevor, your help's much appreciated.

You are welcome sometimes a second opinion can help. Please to read you now have a working solution.

 

[MrBedo]

I have highlighted the And statements above, and based on the description of your issue, it is doubtful that all of the defined conditions will be true at the same time. Since that is what would be required for your statement to work, it would never be true. Changing And to Or could resolve the problem and allow the statement to work as intended.

Thanks for the reply. I actually had Or in there originally and was getting the same result, And was what was in there by the time I copied and pasted the code into my original post.

I appreciate what you're saying though and you're absolutely right, the logic of the statement is incorrect for what I'm trying to achieve. But it was like that only after I'd tried all sorts of other things and was more of an 'Oh well, let's see what this does' kinda thing!

Thanks for your input though, appreciate the feedback.