f



MS ACCESS 2002: How to DESIGN a semi-complex QUERY EXPRESSION FIELD???

Ok, my Access 2002 language writing skills are VERY rusty,.  I would
know how to do what I need using SQL Server's "Coalesce' function, but
I don't have that available to me in the Access 2002 database I'm
currently programming.

....So could someone advise me how to add an Expression Field to a
Access 2002 Query that will reflect the following:

Imagine I want a field that derives from Fields in a table I've called
tblContacts.  tblContacts has fields called FirstName, LastName and
CompanyName.

Here's what I want the Expression Field (which I'll call 'FullName')
to reflect:

1)  If neither FirstName nor LastName are NULL, then FullName should
be the equivalent of LastName, FirstName (that's the LastName followed
by a comma followed by the FirstName.

2)  If LastName is NULL, but FirstName is not NULL, then FullName
should be the equivalent of FirstName.

3) If both LastName and FirstName are NULL, and CompanyName is not
null, then FullName should be the equivalent of CompanyName.

So that's it.  How would you 'design' this FullName field in Access'
Query Design view?

Thanks in advance.
0
9/13/2006 11:25:07 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

9 Replies
474 Views

Similar Articles

[PageSpeed] 26

Alan Mailer <clarityassoc@earthlink.net> wrote in
news:494hg29rd5hm6r61ljgv66hgea0egnn2tr@4ax.com: 

> Ok, my Access 2002 language writing skills are VERY rusty,.  I
> would know how to do what I need using SQL Server's "Coalesce'
> function, but I don't have that available to me in the Access
> 2002 database I'm currently programming.
> 
> ...So could someone advise me how to add an Expression Field
> to a Access 2002 Query that will reflect the following:
> 
> Imagine I want a field that derives from Fields in a table
> I've called tblContacts.  tblContacts has fields called
> FirstName, LastName and CompanyName.
> 
> Here's what I want the Expression Field (which I'll call
> 'FullName') to reflect:
> 
> 1)  If neither FirstName nor LastName are NULL, then FullName
> should be the equivalent of LastName, FirstName (that's the
> LastName followed by a comma followed by the FirstName.
> 
> 2)  If LastName is NULL, but FirstName is not NULL, then
> FullName should be the equivalent of FirstName.
> 
> 3) If both LastName and FirstName are NULL, and CompanyName is
> not null, then FullName should be the equivalent of
> CompanyName. 
> 
> So that's it.  How would you 'design' this FullName field in
> Access' Query Design view?
> 
> Thanks in advance.
> 
You didn't specify a case where firstname is null but lastname 
is not. The code below will show lastname, 

Fullname: nz(Lastname+", " & Firstname, CompanyName)
Note the use of + and & as concatenation operators. + propagates 
the null, (null + text = null), the & does not propagate the 
null ( null & text = text). the Access nz() function handles 
(value, alternate when value is null). 

-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

0
rquintal (987)
9/14/2006 12:14:28 AM
On 14 Sep 2006 00:14:28 GMT, Bob Quintal <rquintal@sPAmpatico.ca>
wrote:

>Alan Mailer <clarityassoc@earthlink.net> wrote in
>news:494hg29rd5hm6r61ljgv66hgea0egnn2tr@4ax.com: 
>
>> Ok, my Access 2002 language writing skills are VERY rusty,.  I
>> would know how to do what I need using SQL Server's "Coalesce'
>> function, but I don't have that available to me in the Access
>> 2002 database I'm currently programming.
>> 
>> ...So could someone advise me how to add an Expression Field
>> to a Access 2002 Query that will reflect the following:
>> 
>> Imagine I want a field that derives from Fields in a table
>> I've called tblContacts.  tblContacts has fields called
>> FirstName, LastName and CompanyName.
>> 
>> Here's what I want the Expression Field (which I'll call
>> 'FullName') to reflect:
>> 
>> 1)  If neither FirstName nor LastName are NULL, then FullName
>> should be the equivalent of LastName, FirstName (that's the
>> LastName followed by a comma followed by the FirstName.
>> 
>> 2)  If LastName is NULL, but FirstName is not NULL, then
>> FullName should be the equivalent of FirstName.
>> 
>> 3) If both LastName and FirstName are NULL, and CompanyName is
>> not null, then FullName should be the equivalent of
>> CompanyName. 
>> 
>> So that's it.  How would you 'design' this FullName field in
>> Access' Query Design view?
>> 
>> Thanks in advance.
>> 
>You didn't specify a case where firstname is null but lastname 
>is not. The code below will show lastname, 
>
>Fullname: nz(Lastname+", " & Firstname, CompanyName)
>Note the use of + and & as concatenation operators. + propagates 
>the null, (null + text = null), the & does not propagate the 
>null ( null & text = text). the Access nz() function handles 
>(value, alternate when value is null). 
>
>-- 
>Bob Quintal
>
>PA is y I've altered my email address.


Thank you Bob for this interesting solution.  It got me almost all the
way to where I need to be; except for handling (as you mentioned) the
one condition I left out of my original message:

4) If LastName is not null and FirstName is Null, then FullName should
be the equivalent of LastName.

....By the way, one other quibble, your solution deals with condition
#2...

>> 2)  If LastName is NULL, but FirstName is not NULL, then
>> FullName should be the equivalent of FirstName.

....in a way that is not optimal for me.  (Please understand, I'm not
criticising your help, I really appreciate it).  However, using your
solution, when the above condition is met FullName becomes
"FirstName," (in other words, the FirstName followed by an undesired
comma).  Given this discovery, let me throw out a 5th rule I'd like
FullName to follow:

4) A comma should only appear in the FullName expression if both
LastName and FirstName are NOT NULL.

Could you (or anyone else reading this) help me to correct the
otherwise brilliant "Fullname: nz(Lastname+", " & Firstname,
CompanyName)" solution in a way that will accomodate the two new
conditions I've added in this message?

I'm learning a lot from this.  Again, thank you for your help.

0
9/14/2006 3:50:31 AM
By way of update, here's how I've written the Query Expression I've
been asking about:

FullName: IIf(Not IsNull([FirstName]) And Not
IsNull([LastName]),[LastName] & ", " & [FirstName],IIf(Not
IsNull([LastName]),[LastName],IIf(Not
IsNull([FirstName]),[FirstName],[Company])))

....This appears to be giving me what I want, but it seems awfully
convoluted, not read-able, hard to write, etc...

I'm still open to hearing a cleaner way of going about this.  In the
meantime though, thanks to all who have lent a hand.

On Thu, 14 Sep 2006 03:50:31 GMT, Alan Mailer
<clarityassoc@earthlink.net> wrote:

>On 14 Sep 2006 00:14:28 GMT, Bob Quintal <rquintal@sPAmpatico.ca>
>wrote:
>
>>Alan Mailer <clarityassoc@earthlink.net> wrote in
>>news:494hg29rd5hm6r61ljgv66hgea0egnn2tr@4ax.com: 
>>
>>> Ok, my Access 2002 language writing skills are VERY rusty,.  I
>>> would know how to do what I need using SQL Server's "Coalesce'
>>> function, but I don't have that available to me in the Access
>>> 2002 database I'm currently programming.
>>> 
>>> ...So could someone advise me how to add an Expression Field
>>> to a Access 2002 Query that will reflect the following:
>>> 
>>> Imagine I want a field that derives from Fields in a table
>>> I've called tblContacts.  tblContacts has fields called
>>> FirstName, LastName and CompanyName.
>>> 
>>> Here's what I want the Expression Field (which I'll call
>>> 'FullName') to reflect:
>>> 
>>> 1)  If neither FirstName nor LastName are NULL, then FullName
>>> should be the equivalent of LastName, FirstName (that's the
>>> LastName followed by a comma followed by the FirstName.
>>> 
>>> 2)  If LastName is NULL, but FirstName is not NULL, then
>>> FullName should be the equivalent of FirstName.
>>> 
>>> 3) If both LastName and FirstName are NULL, and CompanyName is
>>> not null, then FullName should be the equivalent of
>>> CompanyName. 
>>> 
>>> So that's it.  How would you 'design' this FullName field in
>>> Access' Query Design view?
>>> 
>>> Thanks in advance.
>>> 
>>You didn't specify a case where firstname is null but lastname 
>>is not. The code below will show lastname, 
>>
>>Fullname: nz(Lastname+", " & Firstname, CompanyName)
>>Note the use of + and & as concatenation operators. + propagates 
>>the null, (null + text = null), the & does not propagate the 
>>null ( null & text = text). the Access nz() function handles 
>>(value, alternate when value is null). 
>>
>>-- 
>>Bob Quintal
>>
>>PA is y I've altered my email address.
>
>
>Thank you Bob for this interesting solution.  It got me almost all the
>way to where I need to be; except for handling (as you mentioned) the
>one condition I left out of my original message:
>
>4) If LastName is not null and FirstName is Null, then FullName should
>be the equivalent of LastName.
>
>...By the way, one other quibble, your solution deals with condition
>#2...
>
>>> 2)  If LastName is NULL, but FirstName is not NULL, then
>>> FullName should be the equivalent of FirstName.
>
>...in a way that is not optimal for me.  (Please understand, I'm not
>criticising your help, I really appreciate it).  However, using your
>solution, when the above condition is met FullName becomes
>"FirstName," (in other words, the FirstName followed by an undesired
>comma).  Given this discovery, let me throw out a 5th rule I'd like
>FullName to follow:
>
>5) A comma should only appear in the FullName expression if both
>LastName and FirstName are NOT NULL.
>
>Could you (or anyone else reading this) help me to correct the
>otherwise brilliant "Fullname: nz(Lastname+", " & Firstname,
>CompanyName)" solution in a way that will accomodate the two new
>conditions I've added in this message?
>
>I'm learning a lot from this.  Again, thank you for your help.
0
9/14/2006 4:32:00 PM
"David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in
news:Xns983EAFF7FCDC8f99a49ed1d0c49c5bbb2@127.0.0.1: 

> Alan Mailer <clarityassoc@earthlink.net> wrote in
> news:1q0jg253havict4gerebvj91j8tai0g4g9@4ax.com: 
> 
>> By way of update, here's how I've written the Query
>> Expression I've been asking about:
>> 
>> FullName: IIf(Not IsNull([FirstName]) And Not
>> IsNull([LastName]),[LastName] & ", " & [FirstName],IIf(Not
>> IsNull([LastName]),[LastName],IIf(Not
>> IsNull([FirstName]),[FirstName],[Company])))
>> 
>> ...This appears to be giving me what I want, but it seems
>> awfully convoluted, not read-able, hard to write, etc...
> 
> Nz(Mid(("12" + LastName) & (", " + FirsName), 3), Company)
> 
Wow, that is clever. 


-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

0
rquintal (987)
9/14/2006 9:04:56 PM
Alan Mailer <clarityassoc@earthlink.net> wrote in
news:1q0jg253havict4gerebvj91j8tai0g4g9@4ax.com: 

> By way of update, here's how I've written the Query Expression
> I've been asking about:
> 
> FullName: IIf(Not IsNull([FirstName]) And Not
> IsNull([LastName]),[LastName] & ", " & [FirstName],IIf(Not
> IsNull([LastName]),[LastName],IIf(Not
> IsNull([FirstName]),[FirstName],[Company])))
> 
> ...This appears to be giving me what I want, but it seems awfully
> convoluted, not read-able, hard to write, etc...

Nz(Mid(("12" + LastName) & (", " + FirsName), 3), Company)

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
XXXusenet (2387)
9/14/2006 9:17:54 PM
On Thu, 14 Sep 2006 16:17:54 -0500, "David W. Fenton"
<XXXusenet@dfenton.com.invalid> wrote:

>Alan Mailer <clarityassoc@earthlink.net> wrote in
>news:1q0jg253havict4gerebvj91j8tai0g4g9@4ax.com: 
>
>> By way of update, here's how I've written the Query Expression
>> I've been asking about:
>> 
>> FullName: IIf(Not IsNull([FirstName]) And Not
>> IsNull([LastName]),[LastName] & ", " & [FirstName],IIf(Not
>> IsNull([LastName]),[LastName],IIf(Not
>> IsNull([FirstName]),[FirstName],[Company])))
>> 
>> ...This appears to be giving me what I want, but it seems awfully
>> convoluted, not read-able, hard to write, etc...
>
>Nz(Mid(("12" + LastName) & (", " + FirsName), 3), Company)

Thank you for this suggestion.  I'll try it today.

To my surprise, there is no online explanation for "Nz" in Access 2002
online help (at least none that I could find).  If you (or anyone else
reading this) have another moment, could you explain to me exactly
what is being evaluated (and processed) in the statement you've
suggested?  Don't be afraid of being verbose in your explanation, the
more detail the better.  I say this because someone was kind enough to
attempt a shorthand explanation in an earlier message and I have to
admit I still didn't fully grasp what exactly (piece-by-piece) was
going on in the statement.

Thanks again for everyone's help!
0
9/15/2006 2:32:29 PM
How about I'll go one better and post my own explanation of what I
*think* the above expression means... and have someone tell me if (and
where) I'm wrong.

As an evaluation of the following Access query Expression

FullName: Nz(Mid(("12"+[LastName]) & (", "+[FirstName]),3),[Company])

....Here's my dissection:

Nz() :
The 'Nz' part will make sure that the first NON-NULL string in the
statement will be returned.  In our current example, that will be one
of the following values:
-  Mid(("12"+[LastName]) & (", "+[FirstName]),3)
-  [Company]


Mid() :
("12"+[LastName]) & (", "+[FirstName]),3)
The 'Mid' statement above will start the resulting string from the 3rd
character in the string.  For example: 
-  "12Smith, John" will become "Smith, John".
-  "12Smith" will become "Smith".
-  ", John" will become "John".


,[Company]) :
If the above 'Mid' statement evaluates as Null, Nz will cause
[Company] to be the result of the expression.


....Have I got this right?
0
9/15/2006 3:11:01 PM
Bob Quintal <rquintal@sPAmpatico.ca> wrote in
news:Xns983EB73A87E8DBQuintal@66.150.105.47: 

> "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in
> news:Xns983EAFF7FCDC8f99a49ed1d0c49c5bbb2@127.0.0.1: 
> 
>> Alan Mailer <clarityassoc@earthlink.net> wrote in
>> news:1q0jg253havict4gerebvj91j8tai0g4g9@4ax.com: 
>> 
>>> By way of update, here's how I've written the Query
>>> Expression I've been asking about:
>>> 
>>> FullName: IIf(Not IsNull([FirstName]) And Not
>>> IsNull([LastName]),[LastName] & ", " & [FirstName],IIf(Not
>>> IsNull([LastName]),[LastName],IIf(Not
>>> IsNull([FirstName]),[FirstName],[Company])))
>>> 
>>> ...This appears to be giving me what I want, but it seems
>>> awfully convoluted, not read-able, hard to write, etc...
>> 
>> Nz(Mid(("12" + LastName) & (", " + FirsName), 3), Company)
>
> Wow, that is clever. 

I didn't originate it. But I can't say that I remember who came up
with it -- it was one of the regulars who isn't around much any
more, someone much cleverer than I. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
XXXusenet (2387)
9/15/2006 9:44:49 PM
Alan Mailer <clarityassoc@earthlink.net> wrote in
news:c9glg294vngkgqdnakhikrodkm5lbit187@4ax.com: 

> How about I'll go one better and post my own explanation of what I
> *think* the above expression means... and have someone tell me if
> (and where) I'm wrong.
> 
> As an evaluation of the following Access query Expression
> 
> FullName: Nz(Mid(("12"+[LastName]) & (",
> "+[FirstName]),3),[Company]) 
> 
> ...Here's my dissection:
> 
> Nz() :
> The 'Nz' part will make sure that the first NON-NULL string in the
> statement will be returned.  In our current example, that will be
> one of the following values:
> -  Mid(("12"+[LastName]) & (", "+[FirstName]),3)
> -  [Company]
> 
> Mid() :
> ("12"+[LastName]) & (", "+[FirstName]),3)
> The 'Mid' statement above will start the resulting string from the
> 3rd character in the string.  For example: 
> -  "12Smith, John" will become "Smith, John".
> -  "12Smith" will become "Smith".
> -  ", John" will become "John".
> 
> ,[Company]) :
> If the above 'Mid' statement evaluates as Null, Nz will cause
> [Company] to be the result of the expression.
> 
> 
> ...Have I got this right?

Yep.

You did leave out that in the Mid():

  Null & Null

will return Null.

But I assume you understood that.

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
XXXusenet (2387)
9/15/2006 9:46:59 PM
Reply:

Similar Artilces:

Create MS/ACCESS database app for non-ms/access users
Is it possible to create an MS/ACCESS database application and package it for users who do not have MS/ACCESS loaded in their PCs? I was told that this is possible, but I don't know what software is needed. Do you know? If so, please answer this post. Thanks. SueB *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Per Sue Bricker: >Is it possible to create an MS/ACCESS database application and package >it for users who do not have MS/ACCESS loaded in their PCs? > >I was told that this is possible, but...

Use ASP to read MS Access query that calls MS Access function
Hi: I have an MS Access query for a table called MyTable. One of my fields in the query is a custom field that looks like "MyField: = MyFunction(ID)" where ID is a field in MyTable. I have ASP code and I can use it to read the query OK when "MyField" is not part of the query. If I add in MyField and then run my ASP code to try to read all the query fields, I get an error message on my web browser which says something to the effect of "cannot find MyFunction." On the Access side, I have MyFunction set up in a module and use the keyword "Public" in front of the function name. What else do I need to do to get my ASP code to run this query. I'm trying to avoid reproducing MyFunction within the ASP code if possible. Thanks. Kevin On Thu, 14 Jun 2007 21:38:19 -0000, k-man <kmccrack@gmail.com> wrote: Sorry, no can do. From ASP you don't have access to the Access VBA interpreter. -Tom. >Hi: > >I have an MS Access query for a table called MyTable. One of my >fields in the query is a custom field that looks like "MyField: = >MyFunction(ID)" where ID is a field in MyTable. > >I have ASP code and I can use it to read the query OK when "MyField" >is not part of the query. If I add in MyField and then run my ASP >code to try to read all the query fields, I get an error message on my >web browser which says something to the effect of "cannot find >MyFunction."...

Possible to keep MS Access interface and migrate the MS Access to MS SQL Server?
Hello all Would it be possible to migrate the MS Access 2000 to MS SQL Server 2000? My application is using MS Access 2000 as database and as user interface such as forms. Now, I want to migrate the backend database from MS Access 2000 to MS SQL Server 2000. However, I want to keep the MS Access 2000 interface. Would it be possible? If I migrate the MS Access to SQL Server, would the queries, back-end VBA, macro, tables and forms be affected? Do I need to change the MS Access data type to SQL server supported data type? Which tool I can use to do the migration? Upsizing wizard or exporting...

MS Access 2002 problem: linked form fields in ODBC queries
Ok, my problem is the following: I have very complicated Access 97 databases that link through ODBC to Sybase databases. Now in some of the forms controls I had queries that used as "where clause" parameters form field values; For example: select foo from bar where a_colmun = [forms]![MyForm]![MyField] This worked in Access 97 but fails in Access 2002 (ODBC failure) when the table *bar* is an ODBC Sybase (in my case) linked table. If *bar* were a local Access table, the query would do fine. if i did select foo from bar where a_colmun = (select [forms]![MyForm]![MyFi...

Access MS-ACCESS database on server from client????
I m currently developing a java based application and want to know how can i make client access database located on the server? "HeMan_Speaks" <Lunar20092010@gmail.com> wrote in message news:d71ce567-0434-48b8-b1d2-bb191706ab45@w8g2000prd.googlegroups.com... >I m currently developing a java based application and want to know how > can i make client access database located on the server? I think you're going to have to expand a little bit more in your question here, as is not 100% quite clear what you're trying to do. You might want to make a little bit of a distinction between MS access, the development system that allows you to write code, build forms, and build reports, and that of the database engine that you choose to use with MS access. When you build an application MS access, you then choose your database engine, that might be oracle, SQL server, or perhaps more often leave the default jet database. So when you say make a client access application, are you talking about a piece of software that you plan to install and each computer? The fact that your introducing the issue of java further complicates your question. Perhaps your question is simply you have some java code running on a server, and with to open a mdb file (an access database file). In this situation you're not really using MS access at all here (you using what is called the jet database engine to read that mdb file). For all the trouble in this type of sce...

MATLAB command to access MS Access database
Hi! Is there a MATLAB command which gives access to a .mdb MS Access database or do I need a special driver? Thanks, Andree Hi Andree Ellert, you can use activeXcontrol to interact with MS-Access database. access = actxserver('Access.Application'); returns the handle for Access. set(access, 'Visible', 1); will show the opened Access window. use get(access) and set(access) commands to find the methods available to call. -Vadivelu M =========== http://www.mathworks.com/access/helpdesk/help/toolbox/database/datatool.shtml "Andree Ellert" <ellert@gmx.net/////\\\\\> wrote in message news:eed9874.-1@webx.raydaftYaTP... > Hi! > > Is there a MATLAB command which gives access to a .mdb MS Access > database or do I need a special driver? > > Thanks, > > Andree Andree Ellert wrote: > > > Hi! > > Is there a MATLAB command which gives access to a .mdb MS Access > database or do I need a special driver? > > Thanks, > > Andree <http://www.mathworks.com/matlabcentral/fileexchange/loadFile.do?objectId=4045&objectType=file> -Hardik ...

request for explanation on access an database in ms access
This is the code that I found on the internet for accessing an ms access database: import java.sql.*; class Test { public static void main(String[] args) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // set this to a MS Access DB you have on your machine String filename = "d:/java/mdbTEST.mdb"; String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="; database+= filename.trim() + ";DriverID=22;READONLY=true}"; // add on to the end // now we can get the connection from the DriverManager Connection con = DriverManager.getConnection( database ,"",""); } catch (Exception e) { System.out.println("Error: " + e); } } } What I don't understand is the declaration of database. What I did was putting the database in the same dir as the sourcecode and use "jdbc:odbc:CafeJolt.mdb". But this doesn't work. And the above code does, why is that??? Actually it's not Java question. It's ODBC question. You have to have ODBC connection. Depending of Windows it's usually something like this (for my Windows 2000): Start->Sttings->Control Panel->Administartive Tools->Data Sources(ODBC)-> User DSN -> [Add]-> Driver to Microsoft Access(*.mdb) === And here, finally you can choose file name. ...

How would you design this MS Access database:
I kind of get the idea of databases, how the fields and keys work, but Im not very sure about designing one... what should be a field, etc. I've read that the design of the database is the most important, so I thought Id get some inputs! The database I wish to create will contain dozens of "Message" types. These messages types will all have the same structure: Each message has a 32 word "data" payload (unsigned 16 bit), and for each of these data payloads, I would also like to associate a Description string. Each data word can be modified from a vb program, but initially I would like to set each of the 32 words for a message to a default value. Finally, I would like to make sure that there is a range check on the data itself so that it never inadvertently gets set to something out of range. So for example, Message 1: ---------------- Data Payload & Default Val: Description Range Data word 0: 0x1234 "Header Info" Valid (0x1200 - 0x1300) Data word 1: 0x4445 "Mailbox Source" Full Range .... Data word 31: 0x8274 "Checksum" Full Range So... Would the database have 32 Fields Named Data 0, Data 1.. Data31? And 32 more fields for description and 32 more fields for Range? This seems like too many fields.. perhaps, Would the 32 words of Data be just one field? In e...

MS Access Database Query
Hi, first of all I have to say I'am new to MS Access, I'm used to database systems like Oracle or MySQL. I created a simple database consisting of a single table and a form to fill in new entrys and search for existing entrys. But the search function that Access generates as I created the proper button is way to simple, I need a function that enables to search in several columns. I saw a few quite simple examples where this was done by DAO, but this doesn't work with a .mdb database, does it? (Access allways replies 'Unknown user datatype' once it gets to the line: 'Dim...

Running a ms access database with access 2003 in Vista
I have a Database that I am trying to run on a new vista computer. Up to now I was running it on a win 98 computer and using access 2003! I installed access 2003 and I can make it run and view the data but I can not save any new records. Has any one seen this problem yet? Thanks Clarence Wollman On Wed, 22 Aug 2007 13:17:13 -0700, cwoll <clarencewollman@gmail.com> wrote: Do you have the same problem with the Northwind sample application? (search your computer for northwind.mdb) -Tom. >I have a Database that I am trying to run on a new vista computer. Up >to now I was running it on a win 98 computer and using access 2003! I >installed access 2003 and I can make it run and view the data but I >can not save any new records. >Has any one seen this problem yet? > >Thanks Clarence Wollman On Aug 22, 7:29 pm, Tom van Stiphout <no.spam.tom7...@cox.net> wrote: > On Wed, 22 Aug 2007 13:17:13 -0700, cwoll <clarencewoll...@gmail.com> > wrote: > > Do you have the same problem with the Northwind sample application? > (search your computer for northwind.mdb) I don't have a problem opening and editing the northwind.mdb. Thanks Hi I found a workaround. This database has a fronted and a backed. When I link them in the linked table manager, I was going threw the network to my computer. The reason I am doing this is when any networked computer opens the shortcut to the front end of this computer the linked table manger knows...

Best way to access a remote MS ACCESS database
What is the best way, via the internet, to access a remote Microsoft Access database? I was thinking of using VPN, but I thought that I would check here to see if there is a better way. Any suggestions? Thanks Do you mean remotely controlling an Access database, or linking tables over the internet? The first is doable, the second is a recipe for disaster. Remote Control: If your remote OS is Windows XP, you can use Remote Desktop (although only 1 person can access the PC at a time; this will change in the upcoming Service Pack for XP, but that may not help you now). Otherwise, GoToMyPC offers similar services, as does Terminal Server (a component of a Windows Server machine), PCAnywhere, et al. Linking to remote tables: You will almost certainly encounter severe problems with corruption, and your performance will almost certainly be so poor as to be unworkable. I attempted this once, with a web server in Canada and my machine in Augusta, GA. Even tiny data requests on a very well optimized data structure took waaaay too long to process ... it's simply not workable, IMO. Other alternatives would be covert the app to a web-based and place your data on a web server. Access/Jet isn't really designed for those workloads, but running a small site with minimal transactions and users can be done. Otherwsie, consider switching to a more robust data platform like MySQL, SQL Server, etc. -- Scott McDaniel CS Computer Software Visual Bas...

Accessing MS ACCESS database using Data Environment
HI, I have developped a program in Visual Basic 6.0 using MS Access database. I have created all my reports through Data Environment where I have entered the location of my database file (for e.g c:\project\app\test.mdb) in the Connection properties. I have installed my application on another PC using Package & Deployment wizard. When viewing the reports on the other PC, I have to enter everytime the new location of the database. How can I solve this problem, please? nadia wrote: > HI, > I have developped a program in Visual Basic 6.0 using MS Access database. >...

Accessing a MS Access database across two servers
Hi The technical support guys at my company have set up my system so that the server containing the MS Access database is on one server, while the pages that should access the database are on another server. When the pages and database are on the same server, I usually just connect through ODBC. The problem is that I don't know how to set up an ODBC connection to link from one server to a database in another. Anyone know what I am getting at and know of a possible solution, or a link to a site which may help. thanks in advance Brendan "Singularity" <Brendan.Collins@Sin...

Updating MS-Access Database query
hello i need to update my MS-Access database thru matlab. when using the following code using function "updates" it updates the database i.e .......whereClause = 'where month = ''Nov'''..... Here "Nov" is an entry in the database. Now when i assign a variable to the months..ie variable name "MNTH" could contain Jan /Feb etc,.....and i want to pass the variable "MNTH" ie .......whereClause = 'where month = ''MNTH'''..... it DOES NOT update the database and i receive the error .....error in horxcat......

query ms access database from the user
At college i have been given this piece of untidy code because it mixes a gui interface with the console, i prefare one or the other, but its not my code, i understand what it does. It opens the database CarDB by using the java odbc (Open Database Connectivity ) driver and searches for the row make with nissan and displays to the screen as text in a gui text box the columns Registration, Model, Year, Price in the same row as the nissan . I would like the programme to ask the user which car their want to search for . Unfortunately i do not learn at college or have taught myself sql. If i am understanding proberly the line ResultSet rec = st.executeQuery( "SELECT Registration, Model, Year, Price FROM Table1 where Make='nissan'"); is where the query starts, but its hardcoded in, i presume i need to have a line similar to ResultSet rec = st.executeQuery( "SELECT Registration, Model, Year, Price FROM Table1 request Make=' '") or something similar, am i along the correct lines? here is the code.. import java.sql.*; //import javax.swing.JOptionPane; //import javax.swing.JTextArea; import java.io.*; public class Car2 { public static void main(String args[]){ Connection connection; Statement st; String out=""; JTextArea display=new JTextArea(); try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); connection= DriverManager.getConnection("jdbc:odbc:CarDB","","");...

accessing MS Access database fila via ODBC problem
Hi! I'm trying to connect to a MDB file via ODBC like this: char *DSN = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\\dev-cpp\\test\\1.mdb"; SQLAllocEnv(&env); SQLAllocConnect(env, &hdbc); SQLBrowseConnect(hdbc, (UCHAR*)DSN, strlen(DSN), (UCHAR*)buf, BUF_SIZE, (SQLSMALLINT*)&rcvlen); The function SQLBrowseConnect fails and SQLGetDiagRec returns "IM001 Driver does not support this function" Can anyone help me? Thanks! David On S...

Accessing 2 tables having same name but different databases ms-access
Hi, I have an application with MS-Access as backend and JSP/ tomcat as frontend/webserver. For getting better response time(as access is very slow compared to other RDBMS's), I have split the main DB into two DB's on different disks on my server for better performance.I have kept the table names same but changed the DB names. How do I access tables from both the DB's? This is what I have done; I made different connection to the respective DB's i.e. con and con2. When I want to access data, I am using 2 result sets for retriving data from the tables, but getting no result. Here is my code: rs2_1=stmt2_1.executeQuery("select * from Requests where Status='Released' AND LoginName='"+login+"' order by Req_No"); rs=stmt.executeQuery("select * from Requests where Status='Released' AND LoginName='"+login+"' AND Req_No> 5500 order by Req_No"); So, rs & stmt are for latest records and rs2_1 & stmt2_1 correspond the records from older DB. I think, I'm forgetting some small thing but cant get it. Please advice. What do you mean "no result"? Does "next()" return false for both rs2_1 an= d rs? If you're really trying to improve response time, you should probably be ru= nning the two queries in separate threads ... but as you acknowledge, Acces= s may not have as high performance as other RDBMSs. You would also see rel= iability adv...

Use of Access 2002-2003 structure in MS Access 2007
Dear reader, Is it possible to run in Access 2007 an application designed in Access 2002-2003 format? Tanks for any assistance. Kind regards and all the best for 2009 Simon ...

accessing ms access via ODBC without Microsoft Access
Hi, If I have a computer without Microsoft Access installed but with an ODBC connection to a .mdb file - can I still access the 'database' via ODBC? thanks Tim <timasmith@hotmail.com> wrote in message news:1145206985.433826.106120@i40g2000cwc.googlegroups.com... > Hi, > > If I have a computer without Microsoft Access installed but with an > ODBC connection to a .mdb file - can I still access the 'database' via > ODBC? Yes, provided you are running a program prepared in a language that supports ODBC and have installed an ODBC driver on that user'...

how to design good normalizing database in ms access ?
sir, I have one real time project.This project maintain sales, purchase, sales return , purchase return, receipt , payment and debit note given and debit note receiving and more.. In a siuation how to design relational database with good normalized database ? Please help me.. thanking you n.Jaisankar, pnjaisan@yahoo.com ...

MS Access 2003-2002 database connectivity
I am new to MS Access database and want to accomplish following. I have a database and report in MS Access 2003. There are multiple users who wants to access and generate their respective reports (self help). Because of certain license restrictions they can not access 2003 installed on my PC. We have a shared drive with 2002 installed on it. I wanted to accomplish a connection between Access 2003 (my PC) and MS Access 2002 (shared drive). I wanted to have 10 mins data refresh in 2002. Finally users will connect to 2002 and generate their report. Please advice me on how to accomplish this. Any pointers, suggestions, guidance will be really appreciated. Thanks and Regards Piyush same program minus the prettier menu bar icons in A2K3. if you have multiple users accessing at the same time, i'd suggest you look into a front-end, back-end setup. should be able to get the details from the help file. Read the following article of mine..it should help: http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm Note that you can run mixed clients. So, some can be access 2000, some can be access 2002, and some can be 2003.. If you follow the above instructions, all 3 versions can use, and share that data file... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com ...

How to access MS Access from Perl?
Hi, Good day! I am writing some Perl script on the server side for HTML forms that can create/update an MS Access database. Does anyone know of examples how this can be done? Your help is greatly appreciated. -lc Email to luican@yahoo.com bounces. Anyway, try DBI and DBD::ODBC. Or maybe DBD::ADO, but I've never tried it. ---------------------------------------- http://cpan.org http://search.cpan.org/~jurl/DBD-ODBC-1.09/ODBC.pm ... Connect without DSN The ability to connect without a full DSN is introduced in version 0.21. Example (using MS Access): my $DSN = 'driver=Mi...

Use of Access 2002-2003 structure in MS Access 2007 #2
Dear reader, Is it possible to run in Access 2007 an application designed in Access 2002-2003 format? Tanks for any assistance. Kind regards and all the best for 2009 Simon Simon van Beek wrote: > Dear reader, > > > > Is it possible to run in Access 2007 an application designed in > Access 2002-2003 format? > > > > Tanks for any assistance. > > > > Kind regards and all the best for 2009 > > Simon Yes. There might be some issues, though, depending on how you've programmed the db. Best suggestion, in my eyes, would be testing and playing around a little with *a* *copy* of the database and see what turns up. Most likely, you'd need to set the directory in which the database resides, as a trusted location. A very good sumup of "the good, bad and ugly" ;-) of Access 2007 is this list by Allen Browne http://allenbrowne.com/Access2007.html -- Roy-Vidar Simon, Best wishes to you too. One question: why do you use so many carriage returns in your messages? They cause readers having to unnecessary scroll. Thanks, John "Simon van Beek" <SvanBeekNL@Versatel.nl> schreef in bericht news:495f3ebf$0$28733$bf4948fe@news.tele2.nl... > Dear reader, > > > > Is it possible to run in Access 2007 an application designed in Access > 2002-2003 format? > > > > Tanks for any assistance. > > > > Kind regards and all the best for 2009 > > Simon >...

Free JDBC driver to access MS Access database from Linux enviroment.
I'm looking for a free JDBC driver that will allow me to acces a MSAccess database that resides on a Windows server from a JSPapplication that resides on a Redhat Linx server.Any suggestions would be appreciated.Thanks,Tim tim@nocomment.com wrote:> I'm looking for a free JDBC driver that will allow me to acces a MS> Access database that resides on a Windows server from a JSP> application that resides on a Redhat Linux server.> Any suggestions would be appreciated.Sounds like RmiJdbc is a solution.See <http://rmijdbc.objectweb.org>-- Thomas...

Web resources about - MS ACCESS 2002: How to DESIGN a semi-complex QUERY EXPRESSION FIELD??? - comp.databases.ms-access

Resources last updated: 3/21/2016 2:06:26 AM