SQL Variables

Bill_Gates

$163 Billion Dollar Man
Local time
Today, 00:55
Joined
Oct 22, 2010
Messages
11
So, I need to create a query to read values from a table that was created my multiple people.

Some people wrote in the Days Field: 1,2,3,4
While others wrote: 1.2.3.4
While others wrote: ALL

I could just clean up the data, but that might be futile because if we ever want to add data with an automated import, all of my cleaning would just get erased.

So instead, I simple want my query to recognize that all these text values actually mean the same thing.
 
This type of problem is not a programming issue this is a training issue and should be tackled from this angle. Garbage In Garbage Out (GIGO) or as I say SISO.

If you give them the opportunity to enter data as they are you are subject to a multitude of variations. Good Luck.
 
I certainly agree with the GIGO principle, but I think it would take less man-hours to simply write some code rather than go back and fix it all at this junction.

I have raised the point about standardizing how we fill out this field, but of course, this will only be applied to new projects.

So any ideas?
 
Your problem is knowning what is the right and wrong strings

If you decide that 1,2,3,4 is right

and 1.2.3.4 is wrong
and 1. 2. 3. 4. is wrong
and 1,2,3 & 4 is wrong
and ALL is wrong

The first thing is to start of by replacing and removing invalid characters

tmpStr = "1. 2. 3. & 4."

'/ Remove all spaces
tmpStr = Replace(tmpStr," " "")

tmpStr = "1.2.3.&4."

'/Change any & with commas
tmpStr = Replace(tmpStr,"&",",")

tmpStr = "1.2.3.4"


'/Change and dots with commas
tmpStr = Replace(tmpStr,",".",")

tmpStr = "1,2,3,4"
 
Create another table that groups the variables you want together and link to it , eg.

New Table : Daycodes
columns : Days, Code

Rows ;
Days Code
----- ------
1,2,3,4 FOUR
1.2.3.4 FOUR
ALL FOUR

Your query should link to this on the column Days and the where clause should include DayCodes.Code = "FOUR"
 
I am going to try to use the replace function. In order to do this, I need to read the values I want to replace from my table. I want to read text values from a field called 'DAYS RECEIVED'.

I tried using DLookup, and received a 2001 error. My syntax is:

Dim LResult as String

LResult = DLookup("[DAYS RECEIVED]", "[APPLE ORDERS]", "[DAYS RECEIVED = 'ALL']")

Is that the right way to approach the problem? Why am I getting the error?
 
Try this for string values

LResult = DLookup("[DAYS RECEIVED]", "[APPLE ORDERS]", "[DAYS RECEIVED] = 'ALL'")

If [days Received] is a field in your table [apple orders]

JR
 
FYI, Dlookup only retrive the first record of value 'ALL' it finds in the search field.

JR
 
Try this for string values

LResult = DLookup("[DAYS RECEIVED]", "[APPLE ORDERS]", "[DAYS RECEIVED] = 'ALL'")

If [days Received] is a field in your table [apple orders]

JR


That gave me Error 94 - Invalid use of Null. You are right though, it is a field of that table.

It is okay if Dlookup only retrieves the first value because I can just loop through the database and replace each first value.

Although, I used an update query and it seems to have solved my problem. I would still like to know how to do this using VBA however...
 
Okay, so now I need to try and append all my data to a single table.

Final Table = "Apple 2010"

Tables to Add = "Apple March" "Apple April" "Apple June" "Apple July"

Fields to Append = "Product Ordered" "Days Received"

I tried using an append query, but I cannot append data from the same column in multiple tables into the one column in my final table at the same time using design view, and thus this is going to take me forever. (I have many more tables, the four up there are a subset).

So, I need to figure out how to do this in VBA.

I was thinking I would use a loop to do this. I would access one table, append each field, then access the next etc. I know how to create the loop, but I am not clear on how to append all the data.


I know there is CreateQueryDef("MyQuery", sqltext), but what about something like RunQuery(sqltext)??



Never Mind I figured it out. Thanks for all your help.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom