UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need Help With Make-table Query, Access 2010    
 
   
bakersburg9
post Dec 29 2017, 02:42 PM
Post#1



Posts: 5,152
Joined: 2-November 04
From: Downey, CA


Hey, guys - I can't remember being so desperate - I have been spinning my wheels for several hours, and can't figure out why my make-table query causes my db to freeze - I've tried running it as an append, and THAT doesn't work - this is the strangest thing ! The attached db is a fresh database, and the tables are newly-created tables, so there's no "Bloat"

HELP !!!!

I'm at a loss - I've tried everything... pullhair.gif

Edit: This is the CRAZIEST THING!!! I'm so desperate, I even tried converting my make-table query to a select query, and exporting the results to Excel, then importing that Excel file, and it wouldn't even let me do THAT!!!! blue circle of death, blue circle of death... ALL MORNING!!!


Edit 2 - I'm all too familiar with corrupt databases - is it possible for the PROGRAM (MS Access) to be corrupt ? This is SO crazy...
This post has been edited by bakersburg9: Dec 29 2017, 03:03 PM
Attached File(s)
Attached File  MakeTableTest.zip ( 39.1K )Number of downloads: 11
 
Go to the top of the page
 
projecttoday
post Dec 29 2017, 03:05 PM
Post#2


UtterAccess VIP
Posts: 9,831
Joined: 10-February 04
From: South Charleston, WV


I suspect it has something to do with the GetPit function.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
orange999
post Dec 29 2017, 03:25 PM
Post#3



Posts: 1,795
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


What exactly are you trying to do in plain English?

If I test the GetPIT alone with a string "6abc" or "a6abc" or "abc6a", I get 6 returned.
With "ab56cd9g" , I get 56.

Is that what you expect?

Note: I read Robert's response and decided to test GetPIT before doing more.
This post has been edited by orange999: Dec 29 2017, 03:26 PM

--------------------
Good luck with your project!
Go to the top of the page
 
theDBguy
post Dec 29 2017, 03:27 PM
Post#4


Access Wiki and Forums Moderator
Posts: 72,760
Joined: 19-June 07
From: SunnySandyEggo


Hi Steve,

I agree with Robert. Check out the attached using a different function to retrieve the PIT number.

Hope it helps...

Attached File(s)
Attached File  MakeTableTest.zip ( 26.57K )Number of downloads: 6
 

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post Dec 29 2017, 04:30 PM
Post#5


UtterAccess VIP
Posts: 8,487
Joined: 25-October 10
From: Gulf South USA


Hi Steve: PMFJI, but you can use the function you posted - with a modification. You are walking through the Notes field, and looking for the next character. However, you had no check to see if you were at the end of the field (string) and couldn't go any further. So in the case of one record, you reached the end of the string, then asked to go to the next character - which was not there and bombed the function, freezing the app.

You could try this ...

CODE
Public Function GetPIT(strNote As String) As Long

'************* 11/2/17 - once "INV" worked, switched to PIT

If strNote <> "NA" Then

TryNext:
If Not IsNumeric(Left(strNote, 1)) Then
    If Len(strNote) > 1 Then
        strNote = Mid(strNote, 2)
        GoTo TryNext
    Else
        Exit Function
    End If
Else
    GetPIT = Val(strNote)
End If

End If

End Function


HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
bakersburg9
post Dec 29 2017, 05:16 PM
Post#6



Posts: 5,152
Joined: 2-November 04
From: Downey, CA


First of all, that WAS the problem as far as my blue circle of death / freezing issues - *PHEW* ok - here's how the function works RJD, who just replied, wrote it for me, and it's awesome! In the notes field, a free-form text/memo field, the PIT ID number is typed - best case is simply: "PIT ID 1234," but it's every variation on the way it's typed - sometimes they type a hash # also, they will type "PO PIT ID 1234" or "CPR PIT ID 4321" - CPR PIT ID is to be ignored - and of course, PO PIT ID trumps CPR PIT ID - and from all this, just the NUMBER ITSELF is to be pulled out, because there will be joins to another table - here's what he came up with, to be called in a query:
CODE
Public Function GetPIT(strNote) As Long

'************* 11/2/17 - once "INV" worked, switched to PIT

If strNote <> "NA" Then

TryNext:
If Not IsNumeric(Left(strNote, 1)) Then
strNote = Mid(strNote, 2)
GoTo TryNext
Else
GetPIT = Val(strNote)
End If

End If

End Function


Thanks to everyone for their input !!!! cool.gif

and Joe, I will use your function - thanks !


Steve

Attached File(s)
Attached File  CPR_PIT_ID.png ( 53.02K )Number of downloads: 4
 
Go to the top of the page
 
RJD
post Dec 29 2017, 05:39 PM
Post#7


UtterAccess VIP
Posts: 8,487
Joined: 25-October 10
From: Gulf South USA


thumbup.gif

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
bakersburg9
post Dec 29 2017, 05:40 PM
Post#8



Posts: 5,152
Joined: 2-November 04
From: Downey, CA


What's the difference between:
CODE
Switch([PIT_ID]=0

and
CODE
Switch([PIT_ID]is null

??
Go to the top of the page
 
RJD
post Dec 29 2017, 07:01 PM
Post#9


UtterAccess VIP
Posts: 8,487
Joined: 25-October 10
From: Gulf South USA


Well, these two things check two different conditions. The Is Null is only True if the field is Null, and nothing else, otherwise the result is False. The =0 is only True if the value in the field is 0 - and nothing else, not even Null - otherwise the result is False. In Switch, the first part condition yields only True or False ... If True, the very next component contents are executed. If False, the next component is skipped and the next condition is checked, and so on ...

Is this what you are asking? Or do you need to check for either condition?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th July 2018 - 06:29 PM