Solved Why this code not work

Z

zezo2021

Member
Local time
Today, 08:49
Joined
Mar 25, 2021
Messages
390
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


Code: 
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
 

Attachments

  • Screenshot 2023-05-15 165925.png
    Screenshot 2023-05-15 165925.png
    3.1 KB · Views: 103
I haven't got a computer in front of me, but shouldn't:
Code: 
listSearch.List(i, 0)
be:
Code: 
listSearch.ListItem(i, 0)
?
 
Hello

the problem does not add an item

the problem does not get the correct data
 
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?
 
The_Doc_Man said:
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?
Click to expand...

:)

what should i do?
 
zezo2021 said:
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


Code: 
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
Click to expand...
> 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.
 
I changed the code
but also display this error

Code: 
 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
 

Attachments

  • error.png
    error.png
    3.7 KB · Views: 101
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.
 
I think it should be more like:
Code: 
  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
 
cheekybuddha said:
I think it should be more like:
Code: 
  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
Click to expand...

did you forget apply your change

you paste same code

just remember your
 
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 - 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.
 
The_Doc_Man said:
David (cheekybuddha) is from Aberdeen in the UK
Click to expand...
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! :LOL:
 
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 <=


Code: 
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
 
cheekybuddha said:
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! :LOL:
Click to expand...

As a moderator, I can see where your IP originates. It is POSSIBLE that it showed me where your ISP is located.
 
zezo2021 said:
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 <=


Code: 
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
Click to expand...

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.
 
The_Doc_Man said:
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.
Click to expand...
(y) (y) (y) (y) (y) (y) (y) (y) (y) (y)
 
You must log in or register to reply here.

Similar threads

Z
Solved Why repeat the last Excel sheet row twice
Replies
1
Views
352
zezo2021
Z
Z
Calculation not work
Replies
1
Views
421
cheekybuddha
cheekybuddha
C
Solved i want to change this code to merge two excel files to one sheet side by side.
Replies
2
Views
457
camiramzi
C
R
Updating Excel spreadsheet from within the Access
Replies
8
Views
898
CJ_London
C
G
Solved Increment a value
Replies
3
Views
573
goncalo
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom