Solved Why this code not work (1 Viewer)

[zezo2021]

when Adding from and to the text box to the code not work

this line

And CDate(Worksheets("Database").Cells(i, "B").Value) >= CDate(Me.txtFrom.Value) And CDate(Worksheets("Database").Cells(i, "B").Value) <= CDate(Me.txtTo.Value) Then

On Error Resume Next

listSearch.clear
If txtSearchABBACCT.Value = "" Then

End If

Worksheets("Database").Activate
Dim LastRow As Integer
LastRow = Worksheets("Database").Cells(Rows.Count, 2).End(xlUp).Row




Dim i As Integer
i = 0



For Each C In Worksheets("Database").Range("D2:D" & LastRow)
        If UCase(C.Value) & "" Like "*" & UCase(txtSearchABBACCT.Value) & "*" And CDate(Worksheets("Database").Cells(i, "B").Value) >= CDate(Me.txtFrom.Value) And CDate(Worksheets("Database").Cells(i, "B").Value) <= CDate(Me.txtTo.Value) Then
            listSearch.AddItem
            listSearch.List(i, 0) = C.Row
            listSearch.List(i, 1) = Worksheets("Database").Cells(C.Row, 3).Value
            listSearch.List(i, 2) = Worksheets("Database").Cells(C.Row, 4).Value
            i = i + 1
        End If
Next C

 

[cheekybuddha]

I haven't got a computer in front of me, but shouldn't:

listSearch.List(i, 0)

be:

listSearch.ListItem(i, 0)

?

 

[zezo2021]

Hello

the problem does not add an item

the problem does not get the correct data

 

[The_Doc_Man]

Looking at the "for each C in..." loop, you have an IF statement that includes ... "Worksheets("Database").Cells(i, "B").Value" - but on the first iteration of that loop, "i" is 0 and the indexes in the Cells object are not 0-based, are they?

 

[zezo2021]

Looking at the "for each C in..." loop, you have an IF statement that includes ... "Worksheets("Database").Cells(i, "B").Value" - but on the first iteration of that loop, "i" is 0 and the indexes in the Cells object are not 0-based, are they?

what should i do?

 

[tvanstiphout]

when Adding from and to the text box to the code not work

this line

And CDate(Worksheets("Database").Cells(i, "B").Value) >= CDate(Me.txtFrom.Value) And CDate(Worksheets("Database").Cells(i, "B").Value) <= CDate(Me.txtTo.Value) Then

On Error Resume Next

listSearch.clear
If txtSearchABBACCT.Value = "" Then

End If

Worksheets("Database").Activate
Dim LastRow As Integer
LastRow = Worksheets("Database").Cells(Rows.Count, 2).End(xlUp).Row




Dim i As Integer
i = 0



For Each C In Worksheets("Database").Range("D2:D" & LastRow)
        If UCase(C.Value) & "" Like "*" & UCase(txtSearchABBACCT.Value) & "*" And CDate(Worksheets("Database").Cells(i, "B").Value) >= CDate(Me.txtFrom.Value) And CDate(Worksheets("Database").Cells(i, "B").Value) <= CDate(Me.txtTo.Value) Then
            listSearch.AddItem
            listSearch.List(i, 0) = C.Row
            listSearch.List(i, 1) = Worksheets("Database").Cells(C.Row, 3).Value
            listSearch.List(i, 2) = Worksheets("Database").Cells(C.Row, 4).Value
            i = i + 1
        End If
Next C

> If UCase(C.Value) & "" Like "*" & UCase(txtSearchABBACCT.Value) & "*"
Why are you not using Like in the second clause?
Also, comment out "On error resume next". You want honest error reporting, and fix those errors, rather than ignoring them.

 

[zezo2021]

I changed the code
but also display this error

 If UCase(C.Value) & "" Like "*" & UCase(txtSearchABBACCT.Value) & "*" And CDate(Worksheets("Database").Cells(i, 2).Value) >= CDate(Me.txtFrom.Value) And CDate(Worksheets("Database").Cells(i, 2).Value) <= CDate(Me.txtTo.Value) Then

 

[The_Doc_Man]

That error, #1004, is the way that Excel tells you that an object you named does not exist. There are other errors for the same purpose. That one comes up when you have a qualified reference (a.b.c is a qualified reference to c going through a and b) and one of the qualifiers doesn't exist.

If you put a breakpoint on the line that fails and take the Debug option, it will show you the line of code that filed. If you hover your mouse cursor over each element of the line that potentially has a value, you will be able to see which one DOESN'T have a value. You can then focus on that element to determine how to fix it.

 

[cheekybuddha]

I think it should be more like:

  If UCase(C.Value & "") Like "*" & UCase(txtSearchABBACCT.Value) & "*" _
  And CDate(Worksheets("Database").Cells(i, 2).Value) >= CDate(Me.txtFrom.Value) _
  And CDate(Worksheets("Database").Cells(i, 2).Value) <= CDate(Me.txtTo.Value) Then

 

[zezo2021]

I think it should be more like:

  If UCase(C.Value & "") Like "*" & UCase(txtSearchABBACCT.Value) & "*" _
  And CDate(Worksheets("Database").Cells(i, 2).Value) >= CDate(Me.txtFrom.Value) _
  And CDate(Worksheets("Database").Cells(i, 2).Value) <= CDate(Me.txtTo.Value) Then

did you forget apply your change

you paste same code

just remember your

 

[cheekybuddha]

Your code: If UCase(C.Value) & "" Like ...
My code If UCase(C.Value & "") Like ...

But actually it makes no difference, since it appears UCase() can take Null as an argument.

For your issue you need to work out which object is not valid. At a guess, it's 'Me'.

 

[zezo2021]

up

 

[The_Doc_Man]

@zezo2021 - Why do you think you need to bump a post that had ongoing conversation, with at least 10 posts today? You have to remember that forum members are in different time zones all around the world, and NO, I'm not kidding. You've got me in USA Central timezone. David (cheekybuddha) is from Aberdeen in the UK, something like six hours difference. You are posting from Egypt, an hour ahead of Greenwich?

You have been told by two members (me and David) that you need to take steps to identify which objects are not valid (based on that error 1004 that you got.) Until you do that, we have nothing we can do for you.

 

[cheekybuddha]

David (cheekybuddha) is from Aberdeen in the UK

Interesting, I'm not sure where you got that I'm in Aberdeen. Haven't been to Scotland for a wee while!

Am actually in London - so the timezone is the same as Aberdeen, however!

 

[zezo2021]

I try all the way
to solve the problem but not work also

I compared the result with one date only but still did not get the correct result

the application ignores the <=

Private Sub txtSearchABBACCT_Change()

On Error Resume Next

listSearch.Clear
If txtSearchABBACCT.value = "" Then
FillLIst
End If

Worksheets("Database").Activate
Dim LastRow As Integer
LastRow = Worksheets("Database").Cells(Rows.Count, 2).End(xlUp).Row





ICounter = 0



For Each C In Worksheets("Database").Range("D2:D" & LastRow)
If Format(CDate(Worksheets("Database").Cells(ICounter + 2, 2).value), "dd/MM/yyyy") <= Format(CDate(Me.cboFrom.Text), "dd/MM/yyyy") Then

            listSearch.AddItem
            listSearch.List(ICounter, 0) = C.Row
            listSearch.List(ICounter, 1) = Worksheets("Database").Cells(C.Row, 3).value
            listSearch.List(ICounter, 2) = Worksheets("Database").Cells(C.Row, 4).value
            ICounter = ICounter + 1

End If


Next C



End Sub

 

[The_Doc_Man]

Interesting, I'm not sure where you got that I'm in Aberdeen. Haven't been to Scotland for a wee while!

Am actually in London - so the timezone is the same as Aberdeen, however!

As a moderator, I can see where your IP originates. It is POSSIBLE that it showed me where your ISP is located.

 

[The_Doc_Man]

I try all the way
to solve the problem but not work also

I compared the result with one date only but still did not get the correct result

the application ignores the <=

Private Sub txtSearchABBACCT_Change()

On Error Resume Next

listSearch.Clear
If txtSearchABBACCT.value = "" Then
FillLIst
End If

Worksheets("Database").Activate
Dim LastRow As Integer
LastRow = Worksheets("Database").Cells(Rows.Count, 2).End(xlUp).Row





ICounter = 0



For Each C In Worksheets("Database").Range("D2:D" & LastRow)
If Format(CDate(Worksheets("Database").Cells(ICounter + 2, 2).value), "dd/MM/yyyy") <= Format(CDate(Me.cboFrom.Text), "dd/MM/yyyy") Then

            listSearch.AddItem
            listSearch.List(ICounter, 0) = C.Row
            listSearch.List(ICounter, 1) = Worksheets("Database").Cells(C.Row, 3).value
            listSearch.List(ICounter, 2) = Worksheets("Database").Cells(C.Row, 4).value
            ICounter = ICounter + 1

End If


Next C



End Sub

OK, I finally see what you are doing now that it is formatted using code tags. Must be that these old eyes are getting older.

You are comparing Format(Cdate....Cells(lCounter)....) to Format(CDate(....cboFrom...), both using a "dd/MM/yyyy" format string. This is a valid date string, but your success now depends on what relation you really were testing. IF your goal was to see if one date was AFTER or BEFORE another (on the calendar), a string comparison using that format template is not going to work like you wanted.

String comparisons go from left to right in Access. Therefore, using string dates, "01/FEB/2023" is EARLIER in sequence than "02/JAN/2023". The string comparison stops at the 2nd character because in this case it finds a difference - 1 vs. 2 - that allows assignment of the > or < relationship.

To actually compare dates for before/after ordering, you should use format string "yyyy/mm/dd" for both. And yes, my suggested format string does not use "MM" but rather uses "mm" for months. And that choice makes a difference. The other alternative would be that you should omit the Format(xxx,"dd/MM/yyyy") for both dates and instead, compare CDate() to CDate(). You could easily get away with that shorter comparison since you weren't storing the date intermediates anyway.

 

[zezo2021]

OK, I finally see what you are doing now that it is formatted using code tags. Must be that these old eyes are getting older.

You are comparing Format(Cdate....Cells(lCounter)....) to Format(CDate(....cboFrom...), both using a "dd/MM/yyyy" format string. This is a valid date string, but your success now depends on what relation you really were testing. IF your goal was to see if one date was AFTER or BEFORE another (on the calendar), a string comparison using that format template is not going to work like you wanted.

String comparisons go from left to right in Access. Therefore, using string dates, "01/FEB/2023" is EARLIER in sequence than "02/JAN/2023". The string comparison stops at the 2nd character because in this case it finds a difference - 1 vs. 2 - that allows assignment of the > or < relationship.

To actually compare dates for before/after ordering, you should use format string "yyyy/mm/dd" for both. And yes, my suggested format string does not use "MM" but rather uses "mm" for months. And that choice makes a difference. The other alternative would be that you should omit the Format(xxx,"dd/MM/yyyy") for both dates and instead, compare CDate() to CDate(). You could easily get away with that shorter comparison since you weren't storing the date intermediates anyway.