By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,078 Members | 1,317 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,078 IT Pros & Developers. It's quick & easy.

Defining a Column alias to use in a function

P: 5
I have a a query with a column that needs to have a data type of Memo, which I am able to achive with this function;
Expand|Select|Wrap|Line Numbers
  1. Public Function MEMO()
  2.  
  3. MEMO (COMMENTS)
  4.  
  5. End Function
I want to define the value in this field instead of it pulling the data from the table with the column COMMENTS. In the query I have assigned the column alias and expression as follows; COMMENTS:"".

Now the Memo data type no longer works because I'm using the alias. I tried to define COMMENTS AS COMMENTS using the following code with no success;
Expand|Select|Wrap|Line Numbers
  1. Public Function MEMO()
  2.  
  3. Dim fieldalias(tc_TelecomMakeTable.comments) As COMMENTS
  4.  
  5. MEMO (fieldalias)
  6.  
  7. End Function
I get no VBA errors in syntax with this code, but it doesn't work.

Thanks
Phil S.
Oct 5 '08 #1
Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,709
It's really not clear what you're trying to do.

I can tell you that using reserved words (Memo) as names for other objects is likely to cause you (and the compiler) some confusion.

NB. Please, if you want anyone to help, explain yourself clearly such that we can understand what you want without having to ask too many questions.
Oct 5 '08 #2

P: 69
It's really not clear what you're trying to do.

I can tell you that using reserved words (Memo) as names for other objects is likely to cause you (and the compiler) some confusion.
I'd agree with NeoPa - you need to sort out the way you are naming objects and then see if your code works. There's lots of help available in this forum and elsewhere on the Internet to help you do this.
Oct 6 '08 #3

Expert Mod 2.5K+
P: 2,545
...and in addition to the comments made by NeoPa and YouMike I would question how you could ever have had function Memo working. According to what you have shown, it is infinitely recursive (calls itself without end). If you look at the function definitions you will see they call themselves, like this:

Expand|Select|Wrap|Line Numbers
  1. Public Function DoStuff ()
  2.   DoStuff ([on an argument not passed through function header])
  3. End Function
Unless you have left out a proper return call from your function, which would be along the lines of

Expand|Select|Wrap|Line Numbers
  1.   DoStuff = <some expression> 
your functions are not just incorrectly named but incorrectly structured as well.

-Stewart
Oct 6 '08 #4

P: 5
Thanks you for your responses, as you can tell I'm new to this, so bear with me.

I am creating a Make Table Query "tc_TelecomMakeTable" to make the table tc_TelecomExport. There is a field in the table named "Comments". The "Comments" field in the table needs to have the data type of Memo. The "Comments" field in the query is labeled with an alias "Comments:""" and assigned no value.

What I think I need is to do is assign the data type of Memo to the Alias field in the Query.

If I use a field from an already existing table as a test for the Memo Object it works, believe it or not, but when I change it to the alias I can’t figure out how to grab the AS Comments from the SQL statement.
Expand|Select|Wrap|Line Numbers
  1. Public Function MEMO() 
  2. MEMO (COMMENTS) 
  3. End Function
And just to make sure I understand Stewart and use proper naming and structure, the above code should be more like this?
Expand|Select|Wrap|Line Numbers
  1. Public Function datatype()
  2. MEMO (Comments)
  3. End Function
I really do appreciate you guy’s assistance and I hope this is detailed enough for you. Again, please bear with me, will get better at this once I understand it better and understand what you need to be able to assist me.

Sincerely,
Phil S.
Oct 6 '08 #5

FishVal
Expert 2.5K+
P: 2,653
Hello, Phil.

Actually it is difficult to get your point.

If you want to create a table or add a field to an existing table you may:
  • Use SQL DDL commands: CREATE TABLE, ALTER TABLE
  • Do it via VBA working with TableDef object.

The code you've posted, to the best of my knowledge, do nothing useful but stack overloading.

Kind regards,
Fish
Oct 6 '08 #6

P: 5
The table and query are already created in Access. The alias is defined in the underlining SQL of the query as "AS Comments".

I need to understand how to define the alias "AS Comments" in VBA to use it in the Memo () function.

And again this code works fine with a field from an existing table with the field name Comments, it’s when I change it the alias “Comments:””” assigning no value in the make table query that it stops setting the data type to Memo.
Expand|Select|Wrap|Line Numbers
  1. Public Function datatype()
  2.     MEMO (Comments)
  3. End Function
Oct 6 '08 #7

NeoPa
Expert Mod 15k+
P: 31,709
Ah. Starting to get the picture.

Your idea of including the return value of a function in your query to indicate that the table needs the resultant field to have a data type of Memo (the return type of the function) is actually quite clever.

Unfortunately, data types in code don't exactly match field types in tables. I'm afraid there is no way to return a Memo value from a function.
Oct 6 '08 #8

FishVal
Expert 2.5K+
P: 2,653
Dear Phil.

If you have anywhere function/sub MEMO either in this database code, referenced library or DLL, then you'd better explain what does it do and/or post its code.

And ... I really don't know how VBA function having the same name as some datatype could affect a query.

Kind regards,
Fish
Oct 6 '08 #9

P: 5
But I am returning the memo data type from the function when I use an existing field as a test, it’s when I change the field to an alias that it stops working.

While in the Access query I alt+F11 and opened VB which has the project I'm working on and I created Module1. This Module contains the code;
Expand|Select|Wrap|Line Numbers
  1. Public Function datatype()
  2.     MEMO (Comments)
  3. End Function
When I run the query with this code, the table is created with the data type memo for the field Comments.

Once I change the Comment field in the query to the Alias Comment:"" then it stops setting it as Memo data type and sets it to the default text data type.

I believe this happens because the field name in the underling SQL changes from dbo_TELECOMMUNICATIONS.COMMENTS to AS Comments and is no longer recognized by the Memo function.


I wish I could send you a stripped down test version, so you can see for yourself that it does set the data type to Memo in the table when it creates it as long as I use an existing field and not an alias.
Oct 6 '08 #10

FishVal
Expert 2.5K+
P: 2,653
...
I wish I could send you a stripped down test version, so you can see for yourself that it does set the data type to Memo in the table when it creates it as long as I use an existing field and not an alias.
No need so far.
Please post both SQL statements you use to create a table.

Additionally, I guess your table resides on SQL server.
Are you talking about Access project linked to SQL server, ODBC linked table or table imported from SQL server?
Oct 6 '08 #11

P: 5
Well guys time to eat some crow, I'm so embarrassed I hate to even write this, so embarrassed I'm starting with my best excuse first.

I was working on this around 3 or 4 in the morning Saturday and when I created this function and grabbed a field from the existing table to test it. I didn't give any thought to the fact that this is the field from the table I wanted to update and is, you guessed it a MEMO field.

So every time I ran it using this field it showed as a MEMO field, what a moron!

I hope you will forgive me for wasting your time and my insistence that it was setting the data type and I hope the laugh you’re going to have on me when you’re telling this one to your friends will be payment enough.

Beyond embarrassed,
Phil S.

PS. Is there any way to have this whole thread deleted
Oct 6 '08 #12

NeoPa
Expert Mod 15k+
P: 31,709
Phil, no need to be embarrassed. At least you had the cojones to stand up and admit it as soon as you realised. That's more than most would :)

I'm afraid that we don't delete threads unless they break our rules. Apart from anything else, it would mean that the time and effort put in by the experts would not be reflected in their post-counts. Not a very accurate reflection I know, but it does.
Oct 7 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.