Needle in a haystack

[PtotheC]

We are faced with a problem where we have a table and need to find “the last log” (From Table1.Field3):

Table1
Field1, Field2, Field3
Ref123456, usedidPC, Log1 01/01/2008: hello1 Text 1 Log2 02/01/2008: hello2 Text 2 Log3 03/01/2008: hello3 Text 3 Log4 04/01/2008: hello4 Text 4 Log5 05/01/2008: hello5 Text 5

We need to:
1) Find “Log5 05/01/2008” from Field3 (we already have an idea what the date we are searching for)
2) Then return all text after that into a table or query

Please can you help?

Thank you

[mshmyob]

OK this can easily be done with a Query.

Assume you have a form (called frmFilter) that calls a Query by clicking on a Command Button. On this form you have a textBox called "txtDate".

In the txtDate box you will enter a date like 05/01/2008

In your query you have all the fields you want to show including Field3.
In the query create a Custom field (call it fldFilter)

In the field value line for your custom field put the following

fldFilter: Mid(Trim([Field3]),Len(Trim([Field3]))-9,10)
 

In the criteria line of your custom field put the following

>=[forms]![frmFilter].[txtDate].[value]
 

Click on the check box for Don't Show for your custom field.

This will have the result of your query showing only results where your Field3 has a date >= to the value you entered in your form.

cheers,

We are faced with a problem where we have a table and need to find “the last log” (From Table1.Field3):

Table1
Field1, Field2, Field3
Ref123456, usedidPC, Log1 01/01/2008: hello1 Text 1 Log2 02/01/2008: hello2 Text 2 Log3 03/01/2008: hello3 Text 3 Log4 04/01/2008: hello4 Text 4 Log5 05/01/2008: hello5 Text 5

We need to:
1) Find “Log5 05/01/2008” from Field3 (we already have an idea what the date we are searching for)
2) Then return all text after that into a table or query

Please can you help?

Thank you

[mshmyob]

Actually I noticed a slight problem with the code. Let me make a change and repost.

cheers,

[mshmyob]

OK here is the revised. I changed the property of the textbox on the form to Short Date. Added the DateValue function to the query.

Assume you have a form (called frmFilter) that calls a Query by clicking on a Command Button. On this form you have a textBox called "txtDate". Set the format as Short Date.

In the txtDate box you will enter a date like 05/01/2008

In your query you have all the fields you want to show including Field3.
In the query create a Custom field (call it fldFilter)

In the field value line for your custom field put the following

fldFilter: DateValue(Mid(Trim([Field3]),Len(Trim([Field3]))-9,10))
 

In the criteria line of your custom field put the following

>=[forms]![frmFilter].[txtDate].[value]
 

Click on the check box for Don't Show for your custom field.

This will have the result of your query showing only results where your Field3 has a date >= to the value you entered in your form.

Let me know if this helps.

cheers,