SQL and VBA Insert Null for Unfound Date

[ECEstudent]

Hi,

I'm trying to search through a table with serial card IDs, Order Numbers, and Ship Dates. I'm search through the Serial Card ID column and making my code work so that if the serial card ID exists and its ship date is between 9/30/2001 and 10/1/2011, then retrieve the associated order number and put in array. All of this works perfectly except for one tiny part : it's not returning a null for either unfound serial Card ID or serial card Ids that exist but their ship dates do not qualify. I would like to return a null value for these fields into the array as well as keep the found values in the array.

The whole point of all of this is so I could count the number of rows for all the found order numbers and return a total value with that number.

Thanks
-Laura

 

[Rx_]

Since you are a student, is putting the value in an array part of the assignment?
Are you using a SQL Statement to search through a table?
By using a SQL Statement with criteria (using code) a SQL RecordCount might be faster.

It is possible to go through one record at a time, however that can get very inefficient for larger tables.

So, give us some general ideas on the method or process of searching through the tables and why it needs to go into an array.
It might be that you can create a custom Recordset Object with the desired results. It might be more flexible depending on your goal.

 

[ECEstudent]

I'm an intern at an industrial company and the whole point of my project is to help the manufacturer look up how many orders and items have been shipped between 9/30/2001-10/1/2011 just by entering the part number for the product they are searching for.
My code has to go through three tables which include different information that I'm interested in:
Table1(Members):
Part Number and associated Parents
Table2(RoicStructure):
Modified Parent and associated SerialCardID
Table3(RepOrderItem):
SerialCardID, ShipDate, Item#, and Order#


The whole point: Take user input (Part Number) and end up with Total Items Found and Total Orders Found. Return to user both totals and if the user wants to see the reason behind the totals, he would then go to a table created which would include the order numbers and the item numbers as well as the totals associated with them.

Does this make sense? I can upload my DB if you would like. Thank you.

 

[Rx_]

Welcome to your internship.
Lets avoid an Array for now and do something simple.
Other people might chip in along the way. There are many ways to accomplish the same results.
This would be my suggestion. Feel free to do what you can and then ask for help as needed.
1. Create a basci Query with the Table 1, 2, 3.
For advanced tips on Queries look at: http://www.btabdevelopment.com/ts/anyversion
You will need to create the relationships between them.
Include only the fields you will need. Set the Sort Order for the returned columns.
This will return all the data... during the design phase, you might use something like the TOP 200 to limit the data to the first 200 records.
Example: http://www.trainsignal.com/blog/office-2007-access-relationships

2. Once the query returns all the records (or TOP 200), add a Parameter into the fields that you want to filter. Looks like Date and Part Number are your prime candidates. Once this is working to some degree, remove the TOP 200 so all records with that criteria are returned.
Some basics: http://office.microsoft.com/en-us/a...-most-recent-or-oldest-dates-HA010096309.aspx

3. When this works OK, use a MAKE TABLE query. This actually takes the results and creates a new Table. The table with the results can be viewed, used to make a report or used for many things.
Example: www.youtube.com/watch?v=RtTV2S9Ec9g‎
4. Once this is done, some VBA code in a form, button or some event can use all of this. The existing (last run) table will be deleted, the part number (and dates) asked for, then the new table created from the query, then perhaps a report or some list box created with that information.

From there, you have a lot of options to enhance the interface.

 

[ECEstudent]

Thanks a lot RX. I'll see what I can do and I'll let you know if I have any more questions

 

[ECEstudent]

Ok. What I've been thinking of doing is since the associated parent needs to be modified before it is searched in the structure column of table2, that I would not include it in the query with the other tables. So the query that I would be creating would only include table2 and table3. Table1 would be modified in code and later on, the values would be searched in the query and later return the answer query to the user once the part number is entered as input. The code that I used to do that for a table was:


If DCount("Structure", "RoicStructure", "[Structure] Like '*" & u & "*'") > 0
Then
Set rst = CurrentDb.OpenRecordset( _
"Select * from RoicStructure where Structure Like'*" & u & "*'")



and so on...

 

[ECEstudent]

how would I do that if I were to search the structure in a query? Would it be the same?

 

[Rx_]

Lets not change anything.
Basically -
Use a Query to set the relationships between table 1, 2, 3.
The query will become a Virtual Table.

After that, we can actually use the SQL Statement to do the other things you need very efficiently. All with out changing any of your table structures.

 

[ECEstudent]

What I've done so far since we last communicated is that I created a query linking table 2 and table 3. I left the line of code that takes in the user input (part number) and looks up the parents in table1 and then later modifies them and looks them up in the created query the same. It compiles and it looks good so far. What I am now stuck on is the total. I know how to calculate how many rows there are in sql but I want to actually see the modified query or at least what the total comes out to me. How do print that message out?


This is what I have so far:

Set rst = CurrentDb.OpenRecordset( _
"Select * from Members where username= '" & txtUserName & "'")

While rst.EOF = False
ReDim Preserve Arr(i)
Arr(i) = rst.Fields("ParentProductNbr") 'Insert parent fields into array
i = i + 1
rst.MoveNext
Wend 'end of while loop
x = Arr

For Each varCode In x 'go through each record in array
varCode = Replace(varCode, "-", "*") 'replace each - with *
Dim p As Variant
Dim u As Variant
u = varCode

If DCount("Structure", "CalculateTotal", "[Structure] Like '*" & u & "*'") > 0 Then
MsgBox "Structure Found!" 'print to user
Set rst = CurrentDb.OpenRecordset( _
"Select * from CalculateTotal WHERE ((Structure) Like'*" & u & "*')AND (((ShipDate) BETWEEN #09/30/2001# AND #10/01/2012#)) ")

Set rst = CurrentDb.OpenRecordset( _
"SELECT COUNT(OrderNumber) AS TotalOrders FROM CalculateTotal")

 

[ECEstudent]

CalculateTotal is the name of the query by the way.

How do I return the total number of orders (the last line of code above)? Also, I want to return the result query. Is that possible?

 

[ECEstudent]

Okay. Here is my DB. I had to comment a lot of the code out to start coding with a query rather than with multiple arrays.

 

[Rx_]

Oh good, your much more advanced than I indicated. Thanks.
Instead of opening one table with your Select statement (Select * from Members...)
Create a query with all 3 tables. We can use a SQL Statement to do the equal of your Replace(bla bla bla)

SELECT Wells_Areas.Area, Wells.Well_Name, Wells_Status1.Status1 AS [Well Status], States.State_Abrv AS State, Wells_County.County, Wells.API_No AS [API Number], Wells.NodeID, Well_Status_Staking_Does_Well_FieldWorkDate_Land([ID_Wells]) AS [Has Field Work Date LAND], Well_Status_Staking_Does_Well_Have_APD([ID_Wells]) AS [Has APD]
FROM Wells_County INNER JOIN ((Wells_Areas INNER JOIN (Wells_Status1 INNER JOIN Wells ON Wells_Status1.ID_WellStatus1 = Wells.ID_WellsStatus1) ON Wells_Areas.ID_Area = Wells.ID_Area) INNER JOIN States ON Wells.ID_State = States.ID_State) ON Wells_County.ID_County = Wells.ID_County
WHERE (((Wells_Status1.Status1)="staking") AND ((Wells_Areas.ID_Area) Not In (1000)) AND ((Wells.Activity)='A'))
ORDER BY Wells_Areas.Area, Wells.WName, IIf([Wells].[WNumber] Is Null,0,Val([Wells].[WNumber])), Wells.WSection;

Create a New Query - drag in the three tables, if a relationship doesn't exist, then create the links. Switch from design mode to SQL View and back.

SQL can do most of this work for you.
In the silly SQL I added, there are some custom functions. So don't worry if you can't find them anywhere else.
It is the Join clause that will join your tables together.

Once you have a query created and saved, then the query can be called with the CurrentDB.OpenRecordset just as if it was a table.

 

[Rx_]

Didn't have long to mess with it, but it is making more sense now. I couldn't join some of your tables as expected. So, my guess is that you are taking a field in a table and changing it with the replace statement so it will match a field in another table.
You are doing that with an Array.
So, verify that is your approach.

Instead of creating an array, you could create a Query on the same table - and the query could have a contrived field that holds the same result the Replace performs.
The advantage of a query is, the original column format is in one column, the contrived result is in the second column - with the other data.
From there, the Query can be joined with other tables.
The array, changing the data, then using it to go through a record-set is very ingenious.
The problem is: It will work quick on your sample data. If it is run on a recordset with tens of thousands of records, it might get very slow.

Create a new query, goto SQL view - paste the following:
SELECT Members.ParentProductNbr, Members.Username, Members.StructureQuantity, Right([Username],3) AS ShortUserName
FROM Members;

This is an example of creating a contrived column
ShortUserName. This column used in the next query might for example link to another table (e.g. link a query to a table).
I think this is what you are doing with the array.
Got to run, let me know.

 

[Rx_]

Set rst = CurrentDb.OpenRecordset( _
"SELECT COUNT(OrderNumber) AS TotalOrders FROM CalculateTotal")
The rst will have 0 or 1 record with the Count - right? The query can run in the Query window (under SQL view - then run) to verify it.
check for no record.
If rst.eof and rst.bof then msgbox "No records" ' some exit criteria if BoF and EoF are both true - there are no records.

rst.movefirst

MyString = rst.Fields(1) ' what ever the number of the field is

msgbox mystring
use the string value where ever you need it.

Or, for no records returned use:
If rst.RecordCount > 0 Then
Me.txtIPDate = rst.Fields("TotalOrders").value
Previous_IPDate = rst.Fields("Dt_IP_Last").value

 

[ECEstudent]

Table 1 does not relate to any of the other tables. It has to be left alone. Thus I created a relationship between table 2 and table 3 but left table 1 in code so I could look up it's values later on in the created query (b/w table 2 and 3). Everything seems to be working just fine but I need to double check as well as create a way for the user to view the results. this is what my code looks like:

txtUserName = InputBox("Enter Part Number:")

If Not IsNull(txtUserName) Then

If DCount("UserName", "Members", "[UserName] = '" & txtUserName & "'") > 0 Then
MsgBox "Part Number Found!"

Set rst = CurrentDb.OpenRecordset( _
"Select * from Members where username= '" & txtUserName & "'")


While rst.EOF = False
ReDim Preserve Arr(i)
Arr(i) = rst.Fields("ParentProductNbr") 'Insert parent fields into array
i = i + 1
rst.MoveNext
Wend 'end of while loop
x = Arr


For Each varCode In x
varCode = Replace(varCode, "-", "*")


u = varCode

If DCount("Structure", "CalculateTotal", "[Structure] Like '*" & u & "*'") > 0 Then
MsgBox "Structure Found!" 'print to user

Set rst = CurrentDb.OpenRecordset( _
"Select * from CalculateTotal WHERE ((Structure) Like'*" & u & "*')AND (((ShipDate) BETWEEN #09/30/2001# AND #10/01/2012#)) ") 'search associated fields with user input

Set rst = CurrentDb.OpenRecordset( _
"SELECT COUNT(OrderNumber) AS TotalOrders FROM CalculateTotal")



I think what my code has done so far is return all the fields in both tables 2 and 3 associated with the parents of the part number the user typed in. I just need to print that out somehow (through a table or query as well as a textbox to the user). Do you know how to do that? I really appreciate your help. Thanks.

 

[Rx_]

LOL yes, you threw me with that other table.
Congradulations, you are getting to the next point. If you have successfully created a query and are able to pull the needed fields - that is step one.

Would you like to create a Form? Then put your code into the form?
The form should have either a text box or a list box to replace the inputbox. It probably needs a Begin date and end date with calendar controls.
The code should check to see that:
1. The value in the input box exist - if it does just continue - if it doesn't let the user know it is an invalid number
2. Verify the dates are in-fact a valid date (e.g not 8/32/203) and that the begin date is actually before the end date.

Then a Submit Report button -
Would you like the records to show up in a list box? In a report? as a Query view? This last one can do something now, then change later.

 

[Rx_]

A form with two text box of type date - unbound (not connected to a table) could allow the user to but a begin date, end date Lets name then BeginDate and EndDate
Set rst = CurrentDb.OpenRecordset( _
"Select * from CalculateTotal WHERE ((Structure) Like'*" & u & "*')AND (((ShipDate) BETWEEN #" & Me.BeginDate & "# AND #" & Me.EndDate & "#)) ") 'search associated fields with user input using a Form's text box value

Just to give you an idea.

 

[ECEstudent]

Thank a lot RX. That's not really what I'm stuck on right now though...the dates are pretty much set in stone. The form I'll worry about at the very end (if I need to worry about it at all). What I am stuck on is seeing if what my code is doing is even right...and I can check that by returning the values of the order numbers and the item numbers either in a table or query or whatever. I want to also return the item total and the order total in list boxes.

Just let me know if you have any questions or if i'm confusing you. Thanks.

 

[Rx_]

LOL, you are doing some advanced stuff so of course I assumed you knew all of this.

Give me an idea of what you know or need to learn:
Do you have the Immediate Window showing in the code view?
Do you know how to set a break point on a line of code?
Do you understand the Debug.print for troubleshooting?

Usually, when prototyping, I use the Debug Window. You can run a sub or function from the debug window. It is great to check out code during execution.
Example:
While rst.EOF = False
debug.print "Rst.eof and record count is: " & rst.eof & " " & rst.recordcount
ReDim Preserve Arr(i)
Arr(i) = rst.Fields("ParentProductNbr") 'Insert parent fields into array
debug.pring "Array " & i & " = " & Arr(i)
i = i + 1
rst.MoveNext
Wend 'end of while loop
x = Arr
debug.print "x = " & x

' these debug.print statements will show up in the immeidate window
they are just stings with quotes plus a concat of the &

Let me know if that helps or if there is more needed.

 

[ECEstudent]

Hey RX...I actually had to start over bc there were way to many problems

http://www.access-programmers.co.uk/forums/showthread.php?p=1268599#post1268599


I really hope you can help. I'm really stuck...