By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,158 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Needle in a haystack

P: 3
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
Apr 11 '08 #1
Share this Question
Share on Google+
3 Replies


mshmyob
Expert 100+
P: 903
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

Expand|Select|Wrap|Line Numbers
  1. fldFilter: Mid(Trim([Field3]),Len(Trim([Field3]))-9,10)
  2.  
In the criteria line of your custom field put the following

Expand|Select|Wrap|Line Numbers
  1. >=[forms]![frmFilter].[txtDate].[value]
  2.  
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
Apr 11 '08 #2

mshmyob
Expert 100+
P: 903
Actually I noticed a slight problem with the code. Let me make a change and repost.

cheers,
Apr 11 '08 #3

mshmyob
Expert 100+
P: 903
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


Expand|Select|Wrap|Line Numbers
  1. fldFilter: DateValue(Mid(Trim([Field3]),Len(Trim([Field3]))-9,10))
  2.  

In the criteria line of your custom field put the following


Expand|Select|Wrap|Line Numbers
  1. >=[forms]![frmFilter].[txtDate].[value]
  2.  
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,
Apr 11 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.