| Author |
Topic |
|
RagingSpirit
Starting Member
14 Posts |
 Posted - 2004-08-11 : 12:47:01
|
| I was going to place this in "general SQL" but then scrolled down and voila, there's an access folder.Long story short, I need to convert a massive number of Y/N records in a text field into the same records but in a "Yes/No" format. Access won't accept Y/N in the field (they need yes/no) and kept trying to delete my entries.As a result I decided to write a little SQL macro to convert all Ys to Yes's and Ns to No's respectively. Since my scope of knowledge in SQL is almost non existent I ran into a dead end pretty quickly. Any advice? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-11 : 13:47:15
|
| REPLACE(columnToReplaceIn, 'Y/N','Yes/No')Go with the flow & have fun! Else fight the flow :) |
 |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-08-11 : 13:59:53
|
| IIf([table]![field]="Y",-1,0) in an update query |
|
|
|
RagingSpirit
Starting Member
14 Posts |
Posted - 2004-08-11 : 16:39:18
|
| Thanks a lot guys!Replace worked for me......Another question!On second thought, I'll try to figure it out myself... |
|
|
|
RagingSpirit
Starting Member
14 Posts |
Posted - 2004-08-12 : 12:04:50
|
Here's another question. This time it's a bit more difficult.I decided not to waste forum space and just put it into this already existing thread.Here's the situation: I have a form. This form contains a subform which comes from a query. So far so good?The user will apply different filters to the subform in order to just see the records from specific Agencies. I have created several calculated controls. The first calculates the total number of entries being displayed in the subform. I had no problem with this, I simply did count(*). Easy as pie.But now I must count the number of records which have specific words in one of the fields (let's call this field "status"). Say 4 of the 15 entries were "Declined", I need to know how many were declined.I hav spent a LOT of time trying to figure this out.... My eyes hurt.... MS Access help has misled me and I was trying to use "countif" for about 20 minutes and only then did it hit me that this is only an Excel function which doesn't work in Access (or at least I couldn't get it to work). I've tried all sorts of variatons of "count" but nothing seems to help.Dcount worked, but it didn't give me what I needed. Why? Because when using dcount you need to specify a "table name or query name" in the second parameter. If I do this the results of this calculated control will be static: the user applying a filtr to the form does nothing to the value.How can I do this? It seems like such a simple task and yet I can't figure out a way. Will I be forced to write VB Code? I have no experience in that area at all. Thanks in advance!  |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-08-12 : 12:08:16
|
| Look-up "Like" in help. |
|
|
|
RagingSpirit
Starting Member
14 Posts |
Posted - 2004-08-12 : 12:23:21
|
| I looked up like and read the whole page...I really don't see how that helps me tough. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-12 : 12:29:15
|
| is maybe this what you're looking for:select count(*)from MyTableWhere status like '%declined%' -- or in access '*declined*'Go with the flow & have fun! Else fight the flow :) |
|
|
|
RagingSpirit
Starting Member
14 Posts |
Posted - 2004-08-12 : 12:49:41
|
quote:
Originally posted by spirit1
is maybe this what you're looking for:select count(*)from MyTableWhere status like '%declined%' -- or in access '*declined*'
Thanks for the advice, but where does the code go? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-12 : 13:03:14
|
| where do you want it to go? :)) you should put it where you do the filter on specific words - declined in your case.Go with the flow & have fun! Else fight the flow :) |
|
|
|
RagingSpirit
Starting Member
14 Posts |
Posted - 2004-08-12 : 13:10:11
|
Well like I said a calculated control box is supposed to be where the filtering happens.Such code is too complex for it and it says that there is a syntax error when I attempt to type it in: that's the trouble with access, lots of commands but a lot of them can't go where you want them to!! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-12 : 13:12:38
|
| sorry but i'm no expert in accss. haven't worked with it in years.... sorry i can't be more of a help....Go with the flow & have fun! Else fight the flow :) |
|
|
|
RagingSpirit
Starting Member
14 Posts |
Posted - 2004-08-12 : 13:20:55
|
At least you tried helping  . Thanks anyway!Anyone else? |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-08-12 : 13:49:05
|
| The only other things I can think of right now is to use an expression builder and use the InStr() function.Other than that you could link the control to a data source that would be a query like spirit1's above, but you would need to refresh/requery it.Because we have so little information here it is hard to give advice. |
|
|
|
RagingSpirit
Starting Member
14 Posts |
Posted - 2004-08-12 : 14:00:38
|
quote:
Originally posted by drymchaser
Because we have so little information here it is hard to give advice.
Knowing what else would help you? Just say it.  quote:
Originally posted by drymchaser
Other than that you could link the control to a data source that would be a query like spirit1's above, but you would need to refresh/requery it.
Linking to a query. I don't quite see how this would work.Like I mentioned above, if I create another separate query it'd obtain its data straight from the table and therefore the user's changes wouldn't be accounted for, making it useless. The query has to get its data from the form.On a side note, I've never heard of InStr before. However I checked it out and according to [url]http://www.techonthenet.com/access/functions/string/instr.htm[/url] it returns "the position of the first occurence of a string within another string". Don't see how that's helpful because I need to count the total number of times that a string appears in a field. |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-08-12 : 14:46:29
|
| Let's try this.What is the calculated control? (control type)What is the field you need to search? (data type, name, etc.)What do you want the calculated field to display?When do you want to populate the field?When do you want to update the field? |
|
|
|
RagingSpirit
Starting Member
14 Posts |
Posted - 2004-08-12 : 15:30:48
|
quote:
Originally posted by drymchaser
Let's try this.What is the calculated control? (control type)
It's a simple text box. I've created two of them inside the subform (these aren't visible). One counts the total number of applicants from the agency chosen by the user and the second, as I've already stated, is supposed to count the number of records (or applicants, if you will) which were Hired/Rejected/Withdrew their applications, etc. I just need to know how to count one type at first and the rest will simply be done the same way, obvously.  I will place (have already, but it's useless right now) another text box in the main form which will do the required calculations (eg: percentage accepted from the selected agency) and display the results. It will obviously use the two hidden boxes' data.quote:
Originally posted by drymchaser
What is the field you need to search? (data type, name, etc.)
The field that I am searching is a text field, but I thought that this was already apparent. Let's say its name is "Result"quote:
Originally posted by drymchaser
What do you want the calculated field to display?
It is hidden, therefore it won't display anything!quote:
Originally posted by drymchaser
When do you want to populate the field?When do you want to update the field?
I think that I want them to be updated each time the user applies a filter to the subform where all of the etries are listed. The control which shows the total number of applicants (ok, it doesn't show anything but I made an extra text box in the main form which is visible and which allows me to test this) is updated automatically every time the user changes the filter.Can't think of anything else at the moment. |
|
|
|
RagingSpirit
Starting Member
14 Posts |
Posted - 2004-08-13 : 11:40:50
|
| I figured out how to do it on my own... You can stop thinking now. |
|
|
|
|
Macros