updating a field after filtering "inbetween" dates..?? 1

[wshs1]

i have a table "tbl_AFYMO" which has three fields.
1. BDate
2. DDate
3. FM

on my frm_PurRequisition form
Tdate is automatically given. By using that date,
i need to filter and find the 'FM' which TDate is inbetween Bdate and Ddate. am i making sense?
so i need to update 'field1' based on 'Tdate'.
and Tdate needs to be inbeween Bdate and Ddate.

fm | Bdate | Ddate
1 |01/01/06|01/31/06
2 |02/01/06|02/28/06
etc

im guessing i have to use a query.. any help?

 

[kjv1611]

can you possibly give more detail of where tdate falls into this? All you list in your example are fm, bdate, and ddate, but no tdate.

Maybe give a for instance/scenerio, or the same in more detail?

 

[abaldwin]

How about


Update tbl_AFYMO set fm = yourvalue WHERE me.tdate between Bdate and Ddate

You will of course need to properly construct the sql to replace "yourvalue" with the actual value you want and the me.tdate with the properly constructed reference to your control.

That is going to update all records fm field to the new value for all records where the tdate is between the two fields.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[wshs1]

kjv1611, 'Tdate' is a field which returns today's date.
field is locked and the moment user opens the form, it is upated. On that same form, i have a field "txtFYMO".
based on Tdate, i need to update this(txtFYMO) field box.
I need to update txtFYMO with 'fm' but i first need to find which fm to select from. So now, i need to find which makes 'Tdate' = inbetween Bdate and Ddate. After finding that row i need to capture 'fm'. does that make sense?
im guessing i can use like DLookUp?

 

[kjv1611]

So, Bdate and Ddate are some sort of beginning and ending dates, I guess?

So, you need some sort of solution that says something like:

txtFYMO = FM(field) of the record where the record's Bdate is before today's date, and Ddate is after today's date?

If so, then you could use DLookup for that, or either a Query, or either searching through a recordset.

So, if DLookup..

Private Sub SomeEvent()
  txtFYMO = DLookup("[FYMO]", "MyTable", "[Bdate] < '" & _
            Tdate & "' AND [Ddate] = '" & Tdate & "'")
End Sub

Of course, the table/field names may not be exactly what you have, but I would think that would work..

 

[wshs1]

tjv1611, thank you!!! your my hero of the day!!

 

[kjv1611]

So, did that work? If so, good. If not, let us know.

Also, you may want to add in some error handling into the procedure. For example, what happens, if there isn't any such date that fits the criteria you are looking for? You'd want to either have an error message, or either return different data.

 

[wshs1]

actually im getting a typmismatch.
is there a symantic error that im not cathing?
was certain that was gonna work..

 

[kjv1611]

What type of fields are the ones for comparrison?

Are they string, date, number? Are any different?

One thing you can try is taking out the single quotes around the values from the textbox..

Instead of

Private Sub SomeEvent()
  txtFYMO = DLookup("[FYMO]", "MyTable", "[Bdate] < '" & _
            Tdate & "' AND [Ddate] = '" & Tdate & "'")
End Sub

You could try:

Private Sub SomeEvent()
  txtFYMO = DLookup("[FYMO]", "MyTable", "[Bdate] < " & _
            Tdate & " AND [Ddate] = " & Tdate)
End Sub

Note where the single quotations are taken away. At the very tail end of the Dlookup function, I removed:
& "'"

The single quotes - usage of them or no usage is a common mistake in the Dlookup function. I've made it many times.

 

[wshs1]

getting "missing operator" syntax error..
seems like it's capturing the Tdate but..

Tdate, Bdate, Ddate are all "date/time"

and fymo set as "text

 

[kjv1611]

Oh, I got it. Here is what it SHOULD be, b/c they are all dates:

Private Sub SomeEvent()
  txtFYMO = DLookup("[FYMO]", "MyTable", "[Bdate] < #" & _
            Tdate & "# AND [Ddate] = #" & Tdate & "#")
End Sub

Give that a shot!

 

[kjv1611]

Also, here is a helpful link regarding the dlookup function:

[URL unfurl="true"]http://www.mvps.org/access/general/gen0018.htm[/url]