Updating a Continuous Form

[jbento]

I have to copy records each day from a totally different database, and I bring those records up as a excel file and I copy to my table in access.

3 of those fields that are being copied are in a "date/time" format.

The problem is that I want to convert those fields into just a date format and I can use something like this, to do it: DateOnly: DateValue([CustomerSince]).

So the fields that I put into the query to convert that data are: submit_date1: DateValue([submit_date]), planned_start_date1: DateValue([planned_start_date]), & planned_end_date1: DateValue([planned_end_date)]. So as you can see the fields that the information is being copied into are: submit_date, planned_start_date, & planned_end_date. So once the data with the "date/time" values are copied into those fields, I want my value fields to change the "data/time" to just date, so that is what the function like this is doing: submit_date1: DateValue([submit_date]).

So with that, the reason I need to convert that data is because I also have a Search Form, that does not search correctly with the "date/time" information. It has to be Date Only to get the correct search results.

Also, I have users that like to see the "date/time" information, so I would like continue to show that information to users, but when they search, it has to be Date Only for it to give correct search results.

So what I need to do is somehow copy the converted fields with just the dates to a actual field in the table and that way I can search it correctly on the Search form.

So when I copy the data from the other database, I have a button on that table view to open a continuous form with just the records I have copied from the other database.



Is there a way to copy the converted date fields to another field, that will be used on the search form, so the user will get the correct results? It would have to update each record on that continuous form that is showing.

Can this be done?

[pbaldy]

I think you're going about it the wrong way; just modify your search to account for the time values. In a query:

WHERE DateTimeField >= Forms!FormName.TextboxName and DateTimeField < DateAdd("d", 1, Forms!FormName.TextboxName)

[ArviLaanemets]

INT(YourDateTimeValue) returns numeric equivalent of date part of datetime value.
You also can try to convert the result back to date, and use this for comparision <like CDate(Int(YourDateTimeValue))=?>.
I'm not sure, what will you get with CDate(YourDateTimeValue).

[CJ_London]

Agree with Paul, modify your search function to remove the time element

Suggest show your search function if you need help doing that

[jbento]

I am supplying the code below, so if you can help me figure out where to put the piece of code that will get me the results I need, that would be so awesome!!

Dim dbNm As DatabaseDim qryDef As QueryDef
Set dbNm = CurrentDb()


'Constant Select statement for the Query definition
'strSQL = "SELECT tbl_weeklyMAs.ma_id,"
'strSQL = strSQL & "tbl_weeklyMAs.impacts,
tbl_weeklyMAs.submit_date, tbl_weeklyMAs.submit_date_copy, tbl_weeklyMAs.status, tbl_weeklyMAs.title, tbl_weeklyMAs.planned_start_date, tbl_weeklyMAs.planned_start_date_copy, tbl_weeklyMAs.ma_hyperlink, tbl_weeklyMAs.planned_end_date, tbl_weeklyMAs.planned_end_date_copy,"
'strSQL = strSQL & "tbl_weeklyMAs.requestor "
'
strSQL = "SELECT tbl_weeklyMAs.* "
strSQL = strSQL & "FROM tbl_weeklyMAs "
strWhere = "WHERE"


'strOrder = "ORDER BY tbl_weeklyMAs.ma_id;"'


'Set the WHERE clause for the QueryDef if information has been entered into a field on the form


If Not IsNull(Me.cboFilterImpacts) And
Me.cboFilterImpacts <> "" Then '<--If the textbox ma_id and the other text boxes contains no data THEN do nothing
strWhere = strWhere & " (tbl_weeklyMAs.impacts) Like '*" & Me.cboFilterImpacts & "*' AND" '<--otherwise, apply the LIKE statement to the QueryDef
End If


If Not IsNull(Me.txtFilter_ma_id) And
Me.txtFilter_ma_id <> "" Then
strWhere = strWhere & " (tbl_weeklyMAs.ma_id) Like '*" & Me.txtFilter_ma_id & "*' AND"
End If


If Not IsNull(Me.txtFilterSubmitDate) Then
strWhere = strWhere & " (tbl_weeklyMAs.submit_date) >= #" & Me.txtFilterSubmitDate & "# AND"
End If


If Not IsNull(Me.txtFilterSubmitDateEnd) Then
strWhere = strWhere & " (tbl_weeklyMAs.submit_date) <= #" & Me.txtFilterSubmitDateEnd & "# AND"
End If


If Not IsNull(Me.cboFilterStatus) And Me.cboFilterStatus <> "" Then
strWhere = strWhere & " (tbl_weeklyMAs.status) = '" &
Me.cboFilterStatus & "' AND"
End If


If Not IsNull(Me.txtFilterTitle) And Me.txtFilterTitle <> "" Then
strWhere = strWhere & " (tbl_weeklyMAs.title) Like '*" &
Me.txtFilterTitle & "*' AND"
End If


If Not IsNull(Me.txtFilterRequestor) And Me.txtFilterRequestor <> "" Then
strWhere = strWhere & " (tbl_weeklyMAs.requestor) Like '*" &
Me.txtFilterRequestor & "*' AND"
End If


If Not IsNull(Me.txtFilterPlannedStartDate) And Me.txtFilterPlannedStartDate <> "" Then
strWhere = strWhere & " (tbl_weeklyMAs.planned_start_date) >= #" & Me.txtFilterPlannedStartDate & "# AND"
End If


If Not IsNull(Me.txtFilterPlannedEndDate) Then
strWhere = strWhere & " (tbl_weeklyMAs.planned_end_date) <= #" & Me.txtFilterPlannedEndDate & "# AND"
End If


'Remove the last AND from the SQL statement
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
'MsgBox strSQL & " " & strWhere & " " & strOrder
'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qry_Search")
    qryDef.SQL = strSQL & " " & strWhere & " " & strOrder


Dim dbs As DAO.Database, rst As DAO.Recordset
Dim rsCnt As Integer 'the counter


Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("qry_Search", dbOpenDynaset)


With rst
    If .recordcount > 0 Then 'What you want done
    DoCmd.OpenForm "frm_SearchResults"


    Else
       MsgBox "There is no data that meets your criteria.  Please try again.", fbOKOnly, "No Data Found"
    'What you want done
       DoCmd.OpenForm "frm_search_MAs"


  End If
  End With


  Set dbs = Nothing
  Set rst = Nothing


Me.cboFilterImpacts = Null
Me.txtFilter_ma_id = Null
Me.txtFilterSubmitDate = Null
Me.txtFilterSubmitDateEnd = Null
Me.cboFilterStatus = Null
Me.txtFilterTitle = Null
Me.txtFilterPlannedStartDate = Null
Me.txtFilterPlannedEndDate = Null
Me.txtFilterRequestor = Null


ExitHandler:
   Exit Sub


ErrorHandler:


    If Err = 2489 Then
        Resume ExitHandler
    Else
        MsgBox Err.Description
        Resume ExitHandler


End If


Debug.Print strWhere


End Sub

[pbaldy]

Use the DateAdd() function on the form value here. I'd get rid of the = so you don't match midnight of the next day.

strWhere = strWhere & " (tbl_weeklyMAs.planned_end_date) <= #" & Me.txtFilterPlannedEndDate & "# AND"

[jbento]

So are you saying this will remove the time that is copied into this field, and just have the date so it will search correctly?

[pbaldy]

No, I said it would account for the time value. It adds a day to the end date being searched on and searches for "less than" that date. In other words you end up with this type of thing:

WHERE DateTimeField >= #6/23/23# and DateTimeField < #6/24/23#

That gets you all the records for 6/23 regardless of the time value.

[jbento]

Ahhh ok, gotcha brother!

Can you help me modify the code I posted with what I exactly need to get this working.

You were saying to add "DateAdd()"

Please help me modify the code to make this work.


Thank you so much in advance!

[pbaldy]

I showed you above how to modify it:

DateAdd("d", 1, Forms!FormName.TextboxName)

[jbento]

You had the below:
.
Use the DateAdd() function on the form value here. I'd get rid of the = so you don't match midnight of the next day.

strWhere = strWhere & " (tbl_weeklyMAs.planned_end_date) <= #" & Me.txtFilterPlannedEndDate & "# AND"


Then you have the below:

DateAdd("d", 1, Forms!FormName.TextboxName)

My problem is that I don't know exactly where in my code I need to modify.

Do I modify the below somehow?:

If Not IsNull(Me.txtFilterSubmitDate) Then
strWhere = strWhere & " (tbl_weeklyMAs.submit_date) >= #" & Me.txtFilterSubmitDate & "# AND"
End If

So what I am saying is if I have to modify the my code I just pasted above, where and what do I have to change?