• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

My VBA Function Called in My Query is Not Returning Anything

I am using the following function to check for an Assembly #. I used two numbers that I know are present.

I am calling it from the Access Query SQL as such...

TestGetXPanel([Assembly]) AS Test_GetXsite_Panel

It should be returning a "Yes" for those that have this number, but it is returning nothing at all. It's as if it is not doing anything.

Public Function TestGetXPanel(XPanelYN As Variant) As String   '<---


If XPanelYN = "36P4831TASC" Then
    TestGetXPanel = "Yes"
ElseIf XPanelYN = "36P5431TASC" Then
    TestGetXPanel = "Yes"

End If

End Function

Open in new window

0
Rex85
Asked:
Rex85
  • 15
  • 9
  • 9
  • +3
5 Solutions
 
slightwv (䄆 Netminder) Commented:
Are you sure it is getting there?

add a default else to test with:

...
ElseIf XPanelYN = "36P5431TASC" Then
    TestGetXPanel = "Yes"
Else
   TestGetXPanel = "Bob"
End If
...
0
 
Rex85Author Commented:
Everything came back "Bob"
0
 
slightwv (䄆 Netminder) Commented:
Then verify what you are passing in:

...
Else
   TestGetXPanel = ":" & XPanelYN & ":"
...

It might be passed with spaces?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Jeffrey CoachmanCommented:
...just curious
Why the need to declare XPanelYN As a Variant?
...why not just use String?
0
 
HainKurtSr. System AnalystCommented:
according to your code, if you pass

36P4831TASC or 36P5431TASC it returns "Yes" otherwise it returns empty string...

maybe you want something like this...
Public Function TestGetXPanel(XPanelYN As Variant) As String   '<---
  If (XPanelYN = "36P4831TASC") or (XPanelYN = "36P5431TASC") Then
    TestGetXPanel = "Yes"
  Else
    TestGetXPanel = "No"
  End If
End Function

Open in new window

0
 
Rex85Author Commented:
slightwv: It returned the Assembly Number (Left Field) with the Colons on both ends (Right Field)

boag2000: I was re-using old code. I thought of that. I tried subbing String for Variant, but it did not make a difference.
returned-test.jpg
0
 
Rex85Author Commented:
HainKurt:

There are 184 possible numbers to check for in this scenario. I am using the ElseIf structure to step theough them. I don't have a problem with what you suggest, (It would make it easier) but will it work with hundreds of choices? Thanks,
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Why the need to declare XPanelYN As a Variant?"
Because if what is passed is Null, the Function bombs ...

mx
0
 
Rex85Author Commented:
DatabaseMX: Thank you for clarifying the need for that. I didn't know.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"but will it work with hundreds of choices"
Why can you put these values in a Table and do your comparison that way ?

Certainly you are not going to hard code 184 choices in code?

mx
0
 
Rex85Author Commented:
DatabaseMX: Out of ignorance..I was. How can I use a table? A Join of some kind?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"A Join of some kind?"
Probably, or at least a SubQuery 'lookup' ... depending on exactly what you are trying to do ...

Can you state in words what you are trying to accomplish ?

mx
0
 
HainKurtSr. System AnalystCommented:
create a table, say myList, with one column, say code, put all codes here

then probably you are using it like:

select ..., TestGetXPanel(code) from mytable where ...

-->

select ..., iif(l.code is null, "No","Yes") from myTable t left join myList l on t.code=l.code
0
 
Rex85Author Commented:
Bad Timing on my part, but I have an appointment I have to get to. I will try to put this into words ASAP. Thank you.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Regarding the Function, seems to me the only possibility is that you are actually not passing what you think you are ...

mx
0
 
slightwv (䄆 Netminder) Commented:
>>slightwv: It returned the Assembly Number (Left Field) with the Colons on both ends (Right Field)

I do not understand left/right field.

If you see the correct value inside the colons that you have in your IF, then it has to return "Yes".

Check for case sensitivity, a space inside to colons, etc...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
In you code Module Declarations section ... is there any chance you have this:

Option Compare Binary

Instead of

Option Compare Database   'the normal default
0
 
mbizupCommented:
Your original code looks like it should do the trick.

Were these Assemby Numbers copy/pasted from a website by any chance?

I'm asking, because we have a manufacturing/parts list/purchasing database where every now and again things don't match up because of control characters that get copy/pasted in along with part numbers from the web.  Things work as expected if the part numbers are hand-typed.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, except it's not working ... with 'hand typed' numbers ... as I understand it.

mx
0
 
mbizupCommented:
Let's see what the author's response is.

There are no details here about how the [Assembly] information gets into the table.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
By this "I used two numbers that I know are present." ... I assumed the OP is passing hand typed values to the Function ... which has hard code values (eventually 186 !!) ... and it's not working.

And of course, Yes ... it s/b working in that case.

mx
0
 
slightwv (䄆 Netminder) Commented:
Wouldn't control characters show up with the 'colon test' above?  That is sort of what I was going for.  Passing something you aren't thinking you are passing in.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Not really.  Control character such as Chr(9) - tab ... and so on will not.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Or ... sometimes data is padded with Chr(0) ... Null character ... does not show up either.

mx
0
 
mbizupCommented:
Spaces, linefeed, and  somehow 'visible' control characters would - but I don't think that excludes all possibilities.  I think 'matthewspatrick' has some good routines for getting rid of all the extraneous junk you can pick up from web data.
0
 
Rex85Author Commented:
Wow. I missed a lot. Sorry. Dr. appt.

The table with the Assembly value that is being queried here is in Access. To get it into Access, I exported it from SAP R3 to Excel, Saved it as a .xls file, and then Imported it to Access. The Access table lists it as Text Data Type with a field length of 255 (all the defaults there.)

The function module has Option Compare Database at the top.

Thje purpose, is to selectively identify records that have one of the 180 something model numbers. Rather than use a really long WHERE, I wanted to create a field based on the VBA function that would make the WHERE more along the lines of = YES

I have to make seven of these queries and run them weekly. There will be varying quantities of model numbers. The purpose is to track changes in the level of complaints based on changes to pack design to see if better or worse.

HainKurt:...I will try what you suggest

I think that covers all the comments. I hope

0
 
mbizupCommented:
Just as a test, hand type an assembly number into your table, "A1234", for example.

Then temporarily modify your code as follows:

If XPanelYN = "A1234" Then
    TestGetXPanel = "Yes"
Else      
    TestGetXPanel = "No"

End If

Open in new window

Do the results still differ from what you would expect?
0
 
mbizupCommented:
Also, add Option Explicit to the top of your module, under Option Compare Database and make sure that your code still compiles.
0
 
Rex85Author Commented:
mbizup:

That worked. It returned a Yes for that one hand typed number and a No for all else.
0
 
Rex85Author Commented:
It Compiled.
0
 
mbizupCommented:
Okay - that's what slightwv was trying to test for in the first comment (hidden characters).

His test would have shown you any spaces, carriage return/Linefeeds or any other control characters that would make a visible impact.

But from your results here, it sounds like somewhere between your SAP export and the import from Excel, you are picking up control characters that don't have any visible impact other than messing up your comparisons.  Excel for example uses a "newline" character that has no visible impact on Access data.

So what you need to do is remove everything other than plain text from your Assembly field either before importing it into Access or before making your comparisons within Access.

I don't have a function handy to do this... but will send this link out to a couple of people who might.
0
 
Rex85Author Commented:
Thank you.

Based on your idea...I also copied an Assembly number from the source table into the VBA code and ran it in the function. It did return a Yes. The assembly numbers I pasted into the original function came from an excel table pasted into Word to format/ sub and then into the VBA editor.

So, it does not appear to be different, but the data is different.
0
 
slightwv (䄆 Netminder) Commented:
I'm confused now.  I think we all agree there are some 'special' characters being introduced somewhere.  I'm just not sure 'where' anymore.  

What are we troubleshooting?  SAP to Excel to Access or Excel to Word to VBA Editor to Access?
0
 
mbizupCommented:
<So, it does not appear to be different, but the data is different. >

Exactly!

This happens once in a blue moon here, and it drives our Engineers nuts (on the list of things to be fixed)
0
 
Rex85Author Commented:
It looks to me to be in the VBA editor. (The paste from Excel to Word to the VBA editor.) That resutled in the function not working.

But...pasting from the source table itself into the VBA editor made the function work.

Am I correct in assuming it's in the VBA editor paste operation?
0
 
mbizupCommented:
I don't think it's the editor, but the source itself (possibly formatting in Word?)

So from your last comment, the data that is actually in the table is okay, but the data that was copied into your code is not?
0
 
Rex85Author Commented:
Correct. The Assembly data in the table is fine, IF I hand type or copy/ paste from that source table into the editor, then it works

It is the list of Assembly numbers that I was given in Excel, that I copied to Word and then to the editor that seem to be "different" from the data in the table.
0
 
slightwv (䄆 Netminder) Commented:
I believe this was mentioned before:  Likely some Word auto formatting style stuff coming along for the ride.  If you need an intermediate staging area might I suggest good old notepad?
0
 
mbizupCommented:
Try copying the data from Excel directly to the editor in Access, to see if you can definitively pinpoint where the problem is (Word or Excel).  Excel to Access *should* be okay...

If that woks out, maybe you could work in some recordset based code (similar to what MX was describing) based on data linked to or imported from an Excel spreadsheet to define the 184 comparisons rather than using a large IF-Then- Else block

0
 
Rex85Author Commented:
It would appear the problem is Excel. I copied and pasted from the cell in Excel. The Cell appeared to contain the string....36P031ITASC....However, when pasted into the Editor it came out as
 36 P031ITASC...with a space between the first two numbers and the third character.

If I copy from the Edit line in Excel instead of the cell, it copies OK, but that would be as time consuming as anything I've done.

Also....I have yet to successfully create a subquery. I tried what you suggested HainKurt, but my Syntax was so foul, it would not let me save the query.
0
 
slightwv (䄆 Netminder) Commented:
>>If I copy from the Edit line in Excel instead of the cell, it copies OK

Determine what that character is and replace it as partof the process?

That or figure out where it is being introduced and fix it.
0
 
Rex85Author Commented:
I think my basic question here, "Why is my function not working?" has been answered....My data is messed up with hidden characters. So, I'm splitting points as best I can and closing.

Thank you all very much for helping me figure that out.

I may need a follow up of course to actually get it to work without all that hard coding! ...once I figure out how to get around the hidden characters.

Rex
0
 
Rex85Author Commented:
FYI...I imported the Excel table into Access, then Made a Join qry Where the Two Fields Had to Match, and that seems to work fine now. Thank you all!

Rex
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 15
  • 9
  • 9
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now