473,327 Members | 1,930 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Defining a Column alias to use in a function

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
12 5880
NeoPa
32,556 Expert Mod 16PB
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
youmike
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
Stewart Ross
2,545 Expert Mod 2GB
...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
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
2,653 Expert 2GB
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
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
32,556 Expert Mod 16PB
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
2,653 Expert 2GB
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
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
2,653 Expert 2GB
...
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
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
32,556 Expert Mod 16PB
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

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

Similar topics

1
by: Gord | last post by:
Hello, Anybody know how to detect when a user drags between columns in a MSHFlexGrid when resizing column width? (Short of setting up a timer event that continuously monitors column width.)...
3
by: Andrew | last post by:
With command-line interface ( 3.23.37, UNIX Socket ) all is well with column aliasing. However, column aliases disappear in Excel, over ODBC, when there are multiple (joined) tables in the query. ...
12
by: Matt Garman | last post by:
I'd like to create a "custom output facility". In other words, I want an object whose use is similar to std::cout/std::cerr, but offers more flexibility. Instead of simply writing the parameter...
2
by: Stefan Leitich | last post by:
i have the following function: declare prim_col_name Alias for $1; prim_col_val Alias for $2; my_column Alias for $3; link_table Alias for $4; link_col Alias for $5; data_table alias for $6;...
10
by: GML | last post by:
I have a bound datagrid in C# based on an an SQL query. The results are displayed in a Sharepoint 2003 Webpart. I would like to add a new column based on the results of two columns in the...
3
by: PeterZ | last post by:
Hi, In a running C# app with a datagrid control I select all rows in the dataGrid using CTRL-A, I then paste into some other app like notepad or Word but the column headings get left off. Is...
1
by: Dougeth | last post by:
Hi All, I have having a problem conducting an aggregated function off an alias column I create on the fly. I get an error saying SearchType is an invalid column name yet when I remove the Count...
1
by: deepak | last post by:
Hi There, i have a requiremet ,where in i have a view which has some alias.so at runtime using an application i will get the info about the view using SP_HELP stored proc,but this only gives me...
1
by: Slapo | last post by:
Hello everyone, I could use some help with this function I've created (see below) that runs flawlessly on PostgreSQL 8.3.6 on my development machine but ends with an error on the production server...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.