If statement Correction

E

ECEstudent

Registered User.
Local time
Today, 13:29
Joined
Jun 12, 2013
Messages
153
Hi, I hope someone can help me with this. I'm still researching for help but thought I'd post it here. whichever one helps me get the answer the fastest I guess. Anyways, I'd appreciate any help. Thanks in advance.

I realize the second if statement is incorrect since my code isn't compeletly doing what it's supposed to be doing. What is supposed to be happenening is that after I put values in an array, it's supposed to loop through each value to check if it exists in table 'tbl_LBP Sales Location Num' And if it does, also check that the column 'Rep Region Code' within that same table for that associated value , is not 'INT' nor 'inte'

Code: 
 Set rstt = CurrentDb.OpenRecordset( _
 "Select LEFT (RepId, 3) As RepIdd from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId", dbOpenDynaset, dbSeeChanges)
                    
 While rstt.EOF = False
          ReDim Preserve Arry(n)
          Arry(n) = rstt.Fields("RepIdd")
          n = n + 1
          rstt.MoveNext
Wend

 y = Arry
 VldOrdrNbrDestination = 0
 OrderWOEditing = 0
                    
                    
  For Each varCod In y

        OrderWOEditing = OrderWOEditing + 1
                        
        If DCount("[Location ID]", "[tbl_LBP_Sales Location Num]", "[Location ID] LIKE '*" & varCod & "*'") > 0 Then
    
        If Not "[Rep Region Code]" = "INT" And Not "[Rep Region Code]" = "inte" Then
                            
                    VldOrdrNbrDestination = VldOrdrNbrDestination + 1
                    ' MsgBox varCod
                                
          End If
                            
 End If
                         
  Next varCod
 
Not sure why you want to use an array for this. Try:

Code: 
Dim rstt as Recordset
 
Set rstt = CurrentDb.OpenRecordset("Select DISTINCT LEFT (RepId, 3) As RepIdd from CalculateTotal where [Structure] like '*" & u & "*'")

VldOrdrNbrDestination = 0
OrderWOEditing = 0
                    
While NOT rstt.EOF 
 
    OrderWOEditing = OrderWOEditing + 1
    If DCount("[Location ID]", "[tbl_LBP_Sales Location Num]", "[Location ID] LIKE '*" & rstt.fields(0) & "*'") > 0 Then
        If Not "[Rep Region Code]" = "INT" And Not "[Rep Region Code]" = "inte" Then
            VldOrdrNbrDestination = VldOrdrNbrDestination + 1
        end if
    end if
    rstt.MoveNext

Wend
 
I agree with not using an array, but can't have the field in quotes. ;)
 
Lol thanks CJ London. That's not exactly the problem i'm stuck on though...how do i fix the second IF statement in the code?
 
It's returning values that have INT or inte when they are not supposed to be returned...I want to only return values that don't have INT or inte in column 'Rep Region Code'
 
Should I repeat post 3? Plus I don't think you're referring to the array.
 
I also need the group by statement in the Select statement since it has to return unique values for for each OrderNumber + RepId + Item row. If all three columns are exactly repeated in another row, then only return the unique row. I hope my Select statement before was correct...
 
I'm a little confused. What about post three? Having the fields in quotes? It gives me an error when I take them out
 
Re If statement - Where are you getting [Rep Region Code] from? I assumed it was from somewhere else - the form perhaps. so try restating

Code: 
If Not "[Rep Region Code]" = "INT" And Not "[Rep Region Code]" = "inte" Then
                            
                    VldOrdrNbrDestination = VldOrdrNbrDestination + 1
                    ' MsgBox varCod
                                
          End If
as

Code: 
VldOrdrNbrDestination = VldOrdrNbrDestination +iif(([Rep Region Code] = "INT") + ([Rep Region Code]= "inte")=-1,0,1)
With regards your group by in your original post you are only bringing through the first 3 chars of repID, so grouping by ordernum and item will only produce duplicates if they exist - did you mean to bring through ordernum and item as well? Besides which it is better to use select distinct if you are not using sum, first, last etc and just want to avoid duplicates.

It might be an idea if you can post some sample data together with the result you are trying to achieve to demostrate the problem.
 
This:

"[Rep Region Code]" = "INT"

is comparing two literal strings, not a field against a string. The first part needs to be a valid reference to the value you want to compare to "INT".
 
It returns 'external name not defined' for Rep Region Code when it's not in quotation marks though...
 
Like I said, you need a valid reference to wherever that value is. If it's in the array, you need to refer to the array.
 
How come it works just fine for the first IF statement when its column names are within quotation marks?

If DCount("[Location ID]", "[tbl_LBP_Sales Location Num]", "[Location ID] LIKE '*" & varCod & "*'") > 0 Then
 
Because a domain aggregate function requires it to be in quotes.
 
This doesn't work either...i don't understand what i'm missing here

If DCount("[Rep Region Code]", "[tbl_LBP_Sales Location Num]", "[Rep Region Code]" = "INT") > 0 Then
 
No, it wouldn't. Try

If DCount("[Rep Region Code]", "[tbl_LBP_Sales Location Num]", "[Rep Region Code] = 'INT'") > 0 Then
 
Thanks Pbaldy. It works but it's not recognizing that it needs to work with the If statement above it. It's returning that everything is INT since it recognizes at least 1 INT in the column 'Rep Region Code' when it's supposed to only see if that Location ID value from the statement above it has an INT value...
Thanks a lot!!
 
Thank you guys so much! :) I've tested it and it works great! Doing exactly what i need it to do!
Here is my final code in case someone needs it:

Code: 
Set rstt = CurrentDb.OpenRecordset( _
"Select LEFT (RepId, 3) As RepIdd from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId", dbOpenDynaset, dbSeeChanges)
                    
While rstt.EOF = False
        ReDim Preserve Arry(n)
        Arry(n) = rstt.Fields("RepIdd")
        n = n + 1
        rstt.MoveNext
Wend
y = Arry
VldOrdrNbrDestination = 0
OrderWOEditing = 0
                    
                    
For Each varCod In y
               
    OrderWOEditing = OrderWOEditing + 1
                        
   If DCount("[Location ID]", "[tbl_LBP_Sales Location Num]", "[Location ID] LIKE '*" & varCod & "*' AND NOT [Rep Region Code] = 'INT' AND NOT [Rep Region Code] = 'INT'") > 0 Then
    
                                                     
           VldOrdrNbrDestination = VldOrdrNbrDestination + 1
                                                          
    Else
                        
           MsgBox "RepID with either does not exist or has INT/inte'" & varCod & "'"
                            
    End If
 
You must log in or register to reply here.

Similar threads

E
Counting through vba
Replies
6
Views
788
ECEstudent
E
E
Comparing Rows/Columns in 2 tables
Replies
10
Views
1,086
ECEstudent
E
E
Putting values into temporary table
Replies
13
Views
2,978
Galaxiom
G
E
Temporary Table View
Replies
11
Views
939
ECEstudent
E
E
SQL Select Statement-Space in Column Name
Replies
3
Views
1,663
ECEstudent
E

Users who are viewing this thread

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