Search results

Hi Pat, Thanks for the reply. When I say "portable", I mean portable to different DB systems. An access form, for example, won't be portable to a MYSQL db, for example. Neither will VBA. Interesting that Access is able to interact via ODBC - didn't know that, and it'd be interesting to look...

Hi Jeanette, Thanks for your response, and I totally understand your sentiment. I *greatly* appreciate the time, effort and expertise that volunteers like MStef graciously share! Perhaps I mis-worded my response to MStef. Let my clarify what I meant when I said "something not so...

Thanks so much for your help! While it seemed to work for me, I was looking for a solution that wasn't so "Access-centric". I ended up scripting a solution that worked on an excel file.

Hi Folks, I'm trying to rename a cell when there are duplicates of mutliple columns. Here's an example Col_1 Col_2 Col_3 Note A1 B1 C1 A1 B2 C1 Not a Dup A1 B1 C2 Not a Dup A2 B2 C2 A2 B2 C2 Duplicate A3...

You are not understanding my point, and I think it's because you're living in an Access-GUI-centric world. User access to queries (aka views) is not just via form or report. They can be accessed via ODBC for example, as I mentioned above, or via ResultSet in VBA. Neither one of those methods...

spikepl, i'm sorry you're feeling frustrated. The truth is that I feel the same way; i.e., you're not getting what i'm saying. I understand that you're trying to distinguish between the display format and data type. I agree with you - a display can show any one data type in many different...

Displayed in the datasheet view. Not sure why you think queries are not to be looked at. In any case, the datatype that Access considers that column to be is important. I will be pulling data out of access using ODBC, into other programs, and it will probably be important that Access consider...

I wanted the column displayed as checkboxes. I thought that if Access considered the column to be a boolean data type, it would display them with checkboxes.

the *result* of the IsNull() operation is boolean, but i'm not sure if the data type of the column in the result set is boolean. I'm not totally sure how to check that, though. Perhaps I'd have to do a SELECT INTO instead of a SELECT, or something like that. Perhaps there's a better way...

the resulting column is composed of "0"s and "-1"s. Were it boolean, it would have checkboxes I think.

query designer

the following still doesn't seem to give me a boolean in the result set: Select CBool(NOT(IsNull(a.ID))) as aMatch, a.*, b.* FROM TableA a RIGHT JOIN TableB b ON a.ID = b.ID

i'm interested in the isNull(a.ID) column being a boolean. As it stands, it's numeric (or text - i can't tell which).

taruz, thanks for the reply, but i don't quite catch your drift...

Hi Folks, I'm doing something like the following: Select NOT(IsNull(a.ID)) as aMatch, a.*, b.* FROM TableA a RIGHT JOIN TableB b ON a.ID = b.ID However, the "aMatch" column resulting from the query is either numeric or text. Is there any way I can specify that the resulting column should be a...

hmm, seems like Access only wants you to refer to fields one level of nesting down. The following works: SELECT s.*, sn.SurvT, sn.CE, sn.SD, sn.Dec, sn.Tapr, sf.* FROM ((Species s RIGHT JOIN species_abundances sa ON s.ID = sa.SpeciesID) RIGHT JOIN Lars_seedling_morph_nursery_data_Biotropica...

Thanks GinaWhipp, but none of the errors in the pages above match my error ("JOIN expression not supported"). Any other ideas?

Hi Folks, I have the following query, and i'm getting errors about access not supporting the JOIN. I know I can't embed inner joins within outer joins. But what about right and left joins? When I make them all INNER JOINS or LEFT JOINS, access doesn't complain. If I make them all RIGHT...

Hi Folks, I'm trying to get the following query working in Access 2007, but I get a "Syntax Error (missing operator) in query expression" error. Any help would be greatly appreciated! SELECT i.*, IIF(isNull(b.mID),0,1) LEFT JOIN iTable i ON bTable b WHERE i.mID = b.mID

ok, what a PITA this was. 2 days working on this crap to probably save me half a day's manual entry work! oh well, such is life. I figured I'd post my final code here for all those who might need to do the same thing. Note that I added the ADO library to access VBA prior to running this...