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

FindFirst criteria for multiple fields

P: 12
I'm trying to update a table with information from another table. The tables have 4 fields that should match to allow the update.

Here is the code that is giving me a problem:
Expand|Select|Wrap|Line Numbers
  1. With UpdtTrends
  2.                     .FindFirst ("[FNU]=" & MyTrend!FNU & " AND " & _
  3.                     "[TrendCat_CK]=" & MyTrend!trend_cat_CK & " AND " & _
  4.                     "[Trend_From_Dt]=" & MyTrend!trend_from & " AND " & _
  5.                     "[Trend_To_Dt]=" & MyTrend!trend_to)
  6.                         If Not .NoMatch Then
  7.                             .Edit
  8.                             UpdtTrends!Trend_Fctr = MyTrendCat
  9.                             UpdtTrends.Update
  10.                             MyTrend.MoveNext
  11.  
I don't get an error - it just doesn't find the match in my table (which I know exists).
Mar 9 '10 #1

✓ answered by TheSmileyCoder

Im guessing that [Trend_From_Dt] and [Trend_To_Dt] contains date fields?

If that is the case you need to enclose the date literals your parsing in the string in #

Expand|Select|Wrap|Line Numbers
  1. With UpdtTrends
  2. .FindFirst ("[FNU]=" & MyTrend!FNU & " AND " & _
  3. "[TrendCat_CK]=" & MyTrend!trend_cat_CK & " AND " & _
  4. "[Trend_From_Dt]=#" & MyTrend!trend_from & "# AND " & _
  5. "[Trend_To_Dt]=#" & MyTrend!trend_to & "#")
  6. If Not .NoMatch Then
  7. .Edit
  8. UpdtTrends!Trend_Fctr = MyTrendCat
  9. UpdtTrends.Update
  10. MyTrend.MoveNext

Share this Question
Share on Google+
4 Replies


Delerna
Expert 100+
P: 1,134
The first thing I think about when no match is found but I know there really is a match is
"Does an argument on one side of the equation contain trailing spaces and the argument on the other side doesn't ?

If "Test " = "Test"

evaluates to false even though when you look at the data in a table it appears as though they should match.
Mar 9 '10 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
Im guessing that [Trend_From_Dt] and [Trend_To_Dt] contains date fields?

If that is the case you need to enclose the date literals your parsing in the string in #

Expand|Select|Wrap|Line Numbers
  1. With UpdtTrends
  2. .FindFirst ("[FNU]=" & MyTrend!FNU & " AND " & _
  3. "[TrendCat_CK]=" & MyTrend!trend_cat_CK & " AND " & _
  4. "[Trend_From_Dt]=#" & MyTrend!trend_from & "# AND " & _
  5. "[Trend_To_Dt]=#" & MyTrend!trend_to & "#")
  6. If Not .NoMatch Then
  7. .Edit
  8. UpdtTrends!Trend_Fctr = MyTrendCat
  9. UpdtTrends.Update
  10. MyTrend.MoveNext
Mar 9 '10 #3

Delerna
Expert 100+
P: 1,134
that looks like a good guess
Mar 10 '10 #4

P: 12
Thank you so much - problem solved!
Mar 10 '10 #5

Post your reply

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